Hi there again. I'm integrating the formula but I'm bumping to this wall thing.
Please forgive my nonsense analogy but below is somewhat my database.
Ok, here we go. Column A used to be summarized into just 3 classifications. Yellow, Black, Others(see Column B). But it's now changed to 9 classes (see column C). Due to 9 conditions, I'm breaking it into 2 columns.
Column E (1st batch ok)
=IF(A2="Yellow","Yellow",IF(A2="Yellow + Green","Yellow + Green",IF(A2="Green White","Green White",IF(A2="Green Non-White","Green Non-White",""))))
2nd batch is the problem (this is my approach)
word 'black' and column D is related
This is it
Column F
IF A2 CONTAINS "Black",AND D2 CONTAINS "BAG", if true return "Black - Bag",if(A2 CONTAINS "Black" AND D2 CONTAINS "POUCH", if true return "Black - Pouch",if(A2 CONTAINS "BLACK" AND D2 DOES NOT CONTAIN "POUCH" OR "BAG", if true return "Black", if(AND(A2<> "Yellow",A2<> "Yellow + Green", A2<> "Green White",A2<> "Green Non-White", A2 DOES NOT CONTAIN "BLACK", A2 DOES NOT CONTAIN "Triband", return "Others", If false return "".
Column G
=IF(E2="",F2,E2)
By the way, I've posted this same query in excelforum.com.
http://postimage.org/image/30jv0r1ok