I challenge thee: IF statement for text search with multiple conditions - Help requested

[Monkeyseemonkeytype]

Hi there

I'm looking for some help with an IF statement to define a large (70k+) list of customer I.D's by their order type each week, it's either "Box Only", "Extras Only" or "Both" (as in both boxes and extras), this has already been defined using a different IF statement from the raw data.

Some customers change their order each week, so I want to be able to use a formula to assign a definition to each customer, that identifies them as one of 6 types:

"switches between boxes only and extras-only" if the string contains "Boxes Only" and "Extras Only"
"loyal boxes only customer" if the string only contains "Boxes Only"
"loyal extras only customer" if the string only contains "Extras Only"
"switches between boxes only and both" if the string contains "Boxes Only" and "Both" but not "Extras Only"
"switches between extras only and both" if the string contains "Extras Only and "Both" but not "Boxes Only"
"always orders both" if the string only contains "Both"

("No Order" can be ignored)

An example of the data is below, for three customers between weeks 21-32:

data.PNG

I appreciate this is quite complex, but I hope there's someone who can just help me piece it together, as I'm still learning the language of IF statements.

Thank you in advance for your help!

Monkeyseemonkeytype

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Pete_UK]

There is little point in attaching pictures to posts here - many contributors are not able to view them, and any potential helpers would like to play about with formulae to test out solutions for you, and you can't do that with a picture. You should attach a sample Excel workbook instead.

Pete

[Monkeyseemonkeytype]

Thank for your replies, I'm new to this forum so wasn't aware. Hope this data sample is sufficient.

Thanks

example workbook 1.xlsx

[JohnTopley]

Do you ignore "No order" i.e. do we test for this condition?

[Monkeyseemonkeytype]

Hi, no that's right, we don't need to test for the condition of "No Order".

Thanks

[Pepe Le Mokko]

Please,don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[JohnTopley]

See the attached and the table: all formulas are a variant on the one below

=IF(AND(COUNTIF($B6:$M6,"Box Only"),COUNTIF($B6:$M6,"Extras Only"),COUNTIF($B6:$M6,"Both")=0),"TRUE","")

[Monkeyseemonkeytype]

Hi JohnTopley,

I've just applied this to the rest of the data and it's worked brilliantly, thank you I'm extremely grateful! It'll be really useful to refer back to the formula you've provided for similar issues.

Thanks again

[JohnTopley]

Glad you have a solution.

Can you mark the tread SOLVED ("Thread Tools" on the first thread posting).

Thank you.