Help: Trying to boolean search down a column and note matches.

[LightingPop]

Hi! I have a 10,000s column of data which all fall into 1 of 5 categories but are unmarked so far, I need to mark them 1-5.

I am hoping to find a formula that would work as a boolean search of sorts and when a match is found it would copy a 1-5 depending on the category of search.


The A column looks something like this:

cat food
cat food
dog food
human food
pool rental
car rental
boat rental

When I run the formula which searched for "+food -human" it would result in writing a 1 in the adjacent column which is coded to mean pet food but not the "human food" cell.

Any help is greatly appreciated. Thanks!

[jeffreybrown]

Based on your sample maybe this can be a start and you can fill in the criteria for 4 and 5...

Please Login or Register  to view this content.

[LightingPop]

Thanks for the reply I was able to work with it and figure out how to adapt it. The only problem I have been running it is some of my columns have extra information like this:

Copying
Printing
Copying and Printing

Is there anyway to include in the function that you want cells with word X but if word Y appears then skip it. I would picture it to looking like this:

=IF(OR(ISNUMBER(FIND({"Copying" -Printing},W2))),"Copying")

Also under the current formula above how would it deal with finding two matches in one cell like this:

Copying
Printing
Copying and Printing

[jeffreybrown]

It would help quite a bit if you included a sample worksheet with all the possible combinations in the column and the results you would like to see in the next column.