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

[Ace1312]

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

[ken johnson]

Try these...
If Ash-Covered Coin (Qty: 25) is in A1 then...

[Ace1312]

Thanks much 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.

[ken johnson]

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).

[Ace1312]

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?

[RiderofDark]

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.

[ken johnson]