• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Long IF formula there has to be a better way

A

aggie81

Member
=TRIM(IF(NOT(ISERROR(SEARCH("SUPERTUNIA",H2,1))),"SUPERTUNIA",IF(NOT(ISERROR(SEARCH("BEGONIA",H2,1))),

"BEGONIA",IF(NOT(ISERROR(SEARCH("ALYSSUM",H2,1))),"ALYSSUM",IF(NOT(ISERROR(SEARCH("DIANTHUS",H2,1))),

"DIANTHUS",IF(NOT(ISERROR(SEARCH("EUSTOMA",H2,1))),"EUSTOMA",IF(NOT(ISERROR(SEARCH("RE-STOCK",H2,1))),"RESTOCK",IF(NOT(ISERROR(SEARCH("ROSEMARY",H2,1))),"ROSEMARY",IF(NOT(ISERROR(SEARCH("PROCESSING CHG.",H2,1))),"PROCESSING CHG.",IF(NOT(ISERROR(SEARCH("DELIVERY CHG.",H2,1))),"DELIVERY CHG.",IF(NOT(ISERROR(SEARCH("WEDELIA",H2,1))),"WEDELIA",IF(NOT(ISERROR(SEARCH

("KOPSIA",H2,1))),"KOPSIA",IF(NOT(ISERROR(SEARCH("MISCELLANEOUS",H2,1))),"MISC",IF(ISERROR(SEARCH("-",H2,1)),MID(H2,SEARCH(" ",H2,1)+1,SEARCH(" ",H2,SEARCH(" ",H2)+1)-SEARCH(" ",H2,1)),MID(H2,SEARCH(" ",H2,1)+1,SEARCH("-",H2,1)-SEARCH(" ",H2,1)-1)))))))))))))))

I use the above to pull out one word from an exported SQL Server accounting program file.

4" PANSY-ATLAS YELLOW becomes Pansy

1G CLERODENDRUM quadriloculare becomes Clerodendrum

and so on. I then use this to sort on a Pivot Table the amount of sales for a group of plants. My inventory person who enters the items isn't always consistent with his naming structure and before an error is caught, the item is sold and can't be changed.

The errors create #VALUE errors and fixed with the IFs. I have 418 unique plant groups with over 400,000 rows of data.

Any help is appreciated and if it takes the computer a few minutes to calculate this is ok with me.

Thanks for reading and any help is appreciated.

Lee
 
How do I do it? I can post a few here but it you want the export, then??

[pre]
Code: 
Z4S500	MISC. RETURN	08	-982	0.05	-49.1	Landscaper	#VALUE!
Z4S500	MISC. RETURN	08	-750	0.05	-37.5	Landscaper	#VALUE!
Z5G999	5G CORN	01	45	9	405	Independent	#VALUE!
Z5G999	5G PEPPER	01	8	9	72	Independent	#VALUE!
Z5G999	5G COTTON	01	27	9	243	Independent	#VALUE!
Z5G999	5G TOMATO	01	8	9	72	Independent	#VALUE!
Z5G999	5G ONION	01	8	5	40	Independent	#VALUE!
Z1G999	1G CARROT	01	19	4	76	Independent	#VALUE!
Z1G999	1G WHEAT	01	60	4	240	Independent	#VALUE!
Z1G999	1G RICE	01	45	4	180	Independent	#VALUE!
Z5G999	5G TOMATO	01	6	9	54	Independent	#VALUE!
Z5G999	5G ONION	01	8	9	72	Independent	#VALUE!
Z5G999	5G PEPPER	01	8	9	72	Independent	#VALUE!
Z1G999	1G WHEAT	01	45	4	180	Independent	#VALUE!
Z1G999	1G CARROT	01	18	4	72	Independent	#VALUE!
Z4S900		04	1	10	10	Independent	#VALUE!
Z4S900	DELIVERY CHARGE	06	1	10	10	Landscaper	#VALUE!
E4S020A	4" EGGPLANT	02	23	13.5	310.5	Nursery	#VALUE!
P4S180	4" PEPPER	02	71	13.5	958.5	Nursery	#VALUE!
H4S015	4" HERBS	02	25	13.5	337.5	Nursery	#VALUE!
SOIL1	SOIL-PLANTING MED.	01	1	12	12	Independent	#VALUE!
Z5G999	5G TOMATOES	01	15	5	75	Independent	#VALUE!
Z60999	6" DILL	02	1	3	3	Landscaper	#VALUE!
Z4S500	MISC. RETURN	02	-185	0.05	-9.25	Landscaper	#VALUE!
Z4S500	MISC. RETURN	02	-180	0.03	-5.4	Landscaper	#VALUE!
S1G151T	SOLANUM TREE	04	9	3.25	29.25	Landscaper	#VALUE!
SOIL1	SOIL-PLANTING MED.	01	2	16.09	32.18	Independent	#VALUE!
Z3P999	MISC CUTTINGS	02	66	0.6	39.6	Landscaper	#VALUE!
[/pre]
 
Let's say that you have a list of all your items (the prefered list/way you want it to look) in col M, and the text string you'r searching is in col B. Formula then would be:

=INDEX(M:M,MATCH(TRUE,ISNUMBER(SEARCH(M:M,B1)),0))

Confirm this formula by Ctrl+Shift+Enter, not just Enter. You can speed things up a bit changing the two M:M arrays to a smaller, exact fit (such as M2:M1000), if desired.
 
THe INDEX,MATCH doesn't work as well as the IF formula and it takes a really long time because of the size of the array. There are 487,285 rows with 416 Groups. I have the groups arranged from most frequent to least to try and speed it up. I know that I am asking a lot from the program and until 2007 it was impossible. There are 4 years of sales data that I am sifting.

Tried a little VBA but couldn't get it to work with SELECT CASE. I am not familiar with VBA was hoping.

THanks,

Lee
 
Have you considered PowerPivot?

http://chandoo.org/wp/2013/01/21/introduction-to-power-pivot/
 
Hi Lee ,


If you are still looking for a solution , you can email me the file at narayank[at]gmail[dot]com , if you wish.


Narayan
 
I tried exporting to power pivot but I have the 32 bit version Excel 2010 with 9 gigs of ram. It hangs up about half way.

IS there something similar to Access's "Like" comparison in Excel's VBA? I can fall back to the IF statements and try to account for all oddities.

Thanks to all.

Lee
 
I think you can use LOOKUP function in this case.


Please refer this link for the idea on how to use LOOKUP:

http://chandoo.org/forums/topic/search-for-a-word-string-and-return-that-string-if-found
 
You must log in or register to reply here.
Back
Top