Filtering a list of subscribers based on their preferences

[cmh1]

Dear Community, I am hoping you can help me.

Imagine a spreadsheet, where a subscriber can register their preferences (e.g. Flavour, Ingredients), then when a recipe comes up for that matches the user's criteria, then they are identified to receive a communication.

For Example - if we just consider 2 preferences (Flavour and ingredients), imagine the following subscriptions\preferences - these would be in a list in excel (we would use a form, to capture people's preferences).

Note, I put brackets to help distinguish the two preferences. I also used AND and OR statements. So you can see how someone's preferences are like a combination of the two dimensions (Flavour and ingredient).

User 1: (Spicy) AND (Potato)
User 2: (Spicy) AND (Beef or Salmon)
User 3: (Spicy or Salty) AND (Beef)
User 4: (Bitter or Sweet or Salty) AND (Cheese or Pepper or Onion or Potato)
User 5: (Any Flavour) AND (Potato or Salmon)
User 6: (Sour) AND (Any ingredient)
User 7: (Any Flavour) AND (Any ingredient)
etc

So if a recipe for Spicy cuisine using Beef was published, we would get the following result

User 2: (Spicy) AND (Beef or Salmon)
User 3: (Spicy or Italian) AND (Beef)
User 7: (Any Flavour) AND (Any ingredient)

Or if a recipe for Salty cuisine using Potato and Beef was published, the list would be:

User 3: (Spicy or Salty) AND (Beef)
User 4: (Bitter or Sweet or Salty) AND (Cheese or Pepper or Onion or Potato)
User 5: (Any Flavour) AND (Potato or Salmon)
User 7: (Any Flavour) AND (Any ingredient)

And a final example, a Sweet and Sour recipe, using Pepper and Lemon

User 4: (Bitter or Sweet or Salty) AND (Cheese or Pepper or Onion or Potato)
User 6: (Sour) AND (Any ingredient)
User 7: (Any Flavour) AND (Any ingredient)


In terms of the recipes, it would be cool if we could have some kind table, where we can pick the flavours\ingredients for each recipe, then it would somehow trigger a calculation to identify the relevant users.

I really searched as hard as I could, looking at advanced filters, AND\OR conditions etc, but could not find a way (so I tried quite hard before reaching out for help). I know that companies such as ebay have a similar thing, where you can set an alert to email you, when a pair of Shoes, Colour=Black, Size 10 comes up for sale, but of course I dont have the resources behind me for a special tool for that, so I am trying to use excel to build this "recipient calculator" myself - but I'm really stuck.

Any help\advice would be amazing, but I know it is a tricky one... maybe it is not even possible.

Sincerely,

Cmh1

 

[SanjayGMusafir]

Check this -

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Flavour					Ingredient
2	Spicy	Sweet	Salty	Sour	Bitter	Beef	Potato	Salmon	Pepper	Lemon	Onion	Cheese	Flavour	Ingredients	Preference
3		Y		Y					Y	Y			Sweet or Sour	Pepper or Lemon	(Sweet or Sour) AND (Pepper or Lemon)
4	Y		Y			Y				Y		Y	Spicy or Salty	Beef or Lemon or Cheese	(Spicy or Salty) AND (Beef or Lemon or Cheese)
5						Y		Y					Any Flavour	Beef or Salmon	(Any Flavour) AND (Beef or Salmon)
6		Y											Sweet	Any Ingredient	(Sweet) AND (Any Ingredient)
Sheet1

Cell Formulas
Range	Formula
M3:M6	M3	=TRIM(TEXTJOIN(" or ",,IF(A3="Y",A$2,""),IF(B3="Y",B$2,""),IF(C3="Y",C$2,""),IF(D3="Y",D$2,""),IF(E3="Y",E$2,""),IF(COUNTA(A3:E3)=0,"Any Flavour","")))
N3:N6	N3	=TRIM(TEXTJOIN(" or ",,IF(F3="Y",F$2,""),IF(G3="Y",G$2,""),IF(H3="Y",H$2,""),IF(I3="Y",I$2,""),IF(J3="Y",J$2,""),IF(K3="Y",K$2,""),IF(L3="Y",L$2,""),IF(COUNTA(F3:L3)=0,"Any Ingredient","")))
O3:O6	O3	=TRIM("("& M3 &")"&" AND ("&N3&")")