OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED]IF/Find but only moving parts of the cell

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Ace1312
Newbie
Newbie


Joined: 11 May 2010
Posts: 3

PostPosted: Tue May 11, 2010 2:55 pm    Post subject: [SOLVED]IF/Find but only moving parts of the cell Reply with quote

Ash-Covered Coin (Qty: 25)     124,500 Ryo (4980 each)
Ash-Covered Coin (Qty: 5)     25,000 Ryo (5000 each)
Ash-Covered Coin (Qty: 1)     100,000 Ryo
Billy Bucket (Qty: 26)     57,000 Ryo (2192 each)


I already know how to pull info from other sheets, and I plan on pulling this 2 column info into a 4 column set. but, I need the description of the item in one column (column A), just the qty (I.E. 25) in another one, and then just the first number from the second column into C (I.E. 124,500). It won't be hard to get the 4th number using simple math in the cell. I would also like to find a way to then compare column D but only between cells that column A is the same in, this will vary and so i won't know exactly how many times the description will be the same.

Any help would be awesome, I can play around a bit more once I get some ideas to work with.

Thanks for the help,
Regards,
Ace


Last edited by Ace1312 on Thu May 13, 2010 9:52 am; edited 1 time in total
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 11, 2010 5:05 pm    Post subject: Reply with quote

Try these...
If Ash-Covered Coin (Qty: 25) is in A1 then...
Code:
=TRIM(LEFT(A1;FIND("(";A1)-1))
returns "Ash-Covered Coin" and...
Code:
=VALUE(SUBSTITUTE(TRIM(MID(A1;FIND(":";A1)+1;255));")";""))
returns 25.
If 124,500 Ryo (4980 each) is in F1 then...
Code:
=VALUE(TRIM(LEFT(E1;FIND("R";E1)-1)))
returns 124500.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Ace1312
Newbie
Newbie


Joined: 11 May 2010
Posts: 3

PostPosted: Tue May 11, 2010 6:18 pm    Post subject: Reply with quote

Thanks much Very Happy two of the three worked great. I'm trying to look at the one that should be returning the qty number to figure out why it isn't working.

Also, let me rephrase the last part of my first post. I need to find a way to conditionally format the cells so that they will highlight the lowest number in column D but for each set of items (I.E. There are 3 different Ash covered coins, 4 different billy buckets, and 2 different boring bulbs, I need the lowest number of each highlighted.)

Ash-Covered Coin (Qty: 25)     124,500 Ryo (4980 each)
Ash-Covered Coin (Qty: 5)     25,000 Ryo (5000 each)
Ash-Covered Coin (Qty: 1)     100,000 Ryo
Billy Bucket (Qty: 13)     28,500 Ryo (2192 each)
Billy Bucket (Qty: 10)     21,500 Ryo (2150 each)
Billy Bucket (Qty: 10)     25,000 Ryo (2500 each)
Billy Bucket (Qty: 10)     21,500 Ryo (2150 each)
Boring Bulb (Qty: 26)     52,000 Ryo (2000 each)
Boring Bulb (Qty: 50)     24,000 Ryo (480 each)

Thanks again.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 11, 2010 10:37 pm    Post subject: Reply with quote

Here is a sample doc.
http://www.4shared.com/file/0_LP084i/Text_to_columns_simulation.html
I've had to use an array formula to compare each column D value with other column D values with the same column A value so that the appropriate minimum could be determined.
As far as I can tell Conditional Formatting does not recognise array formulas so the array formulas are in column E where either TRUE or FALSE is returned. Then the Conditional formatting simply refers to the value in column E. Column E can be hidden from view.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Ace1312
Newbie
Newbie


Joined: 11 May 2010
Posts: 3

PostPosted: Wed May 12, 2010 7:24 am    Post subject: Reply with quote

ok, one last question, I figured out why I thought that that formula didn't work. Apparently all of my data has 4 spaces behind it. I've tried to find and replace that with nothing, but it won't let me search for spaces. is their a faster way to delete those 4 spaces? or am I going to have to go through manually to do that?
Back to top
View user's profile Send private message
RiderofDark
Newbie
Newbie


Joined: 12 May 2010
Posts: 1

PostPosted: Wed May 12, 2010 7:31 am    Post subject: Reply with quote

To help the OP (since I'm working alongside him on this) explain what's going on...

"Smoke Bombs (Qty: 19)    " is an example of the exact text string that is getting looked at.

I use the formula =TRIM(SUBSTITUTE(RIGHT(G1;LEN(G1)-FIND(":";G1));")";" ")) and I get a text value of 19. I put a VALUE() statement around that whole thing and I get an Err:502 instead of a result of 19 as an integer value.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Thu May 13, 2010 12:36 am    Post subject: Reply with quote

RiderofDark wrote:
To help the OP (since I'm working alongside him on this) explain what's going on...

"Smoke Bombs (Qty: 19)    " is an example of the exact text string that is getting looked at.

I use the formula =TRIM(SUBSTITUTE(RIGHT(G1;LEN(G1)-FIND(":";G1));")";" ")) and I get a text value of 19. I put a VALUE() statement around that whole thing and I get an Err:502 instead of a result of 19 as an integer value.

One possibility is those trailling "invisible" characters are not spaces. They could be non-breaking spaces (CHAR(160)). I tried
Code:
="Smoke Bombs (Qty: 19)"&REPT(CHAR(160);4)
in G1 and your formula inside the VALUE function resulted in Err:502.
One possible solution is to use the SUBSTITUTE function to get rid of the non-breaking spaces...
Code:
=VALUE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(Original_Data.A1;CHAR(160);"");FIND(":";Original_Data.A1)+1;255));")";""))
in my doc
Code:
=VALUE(TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(G1;CHAR(160);"");LEN(SUBSTITUTE(G1;CHAR(160);""))-FIND(":";SUBSTITUTE(G1;CHAR(160);"")));")";" ")))
in your doc

Ken Johnson
EDIT:Replaced "CODE" with "CHAR" in last formula (What was I thinking!?)
EDIT2:Corrected error in penultimate formula where the last parenthesis was not included inside the code frame.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group