Find Unique Combination of 2 columns where both have re-occuring values

[Hanumanji]

Dear Excelisseurs,

I'm somewhat of an excel rookie so bear with me, I'm running flat with this one so I figured it's best to ask you.

Basically I want to return a value in column 2, but where the value in column 1 is equal to what I ask for.

For example in the table below, I want to be able to return "2" from table[Ingredients] where the value in table[Dish] is equal to "A" - which is easy with XLOOKUP. However I also want to be able to return "2" from table[Ingredients] where the value in table[Dish] is "B" or "C" or "D", you get my point. There might be 100s of dishes with the same ingredient, but I need to return the unique combination I ask for.

Dish	Ingredients	Qty	Unit
A	1	5	ml
A	2	7	ml
A	3	4	ml
A	4	3	ml
B	1	1	ml
B	2	10	ml
B	3	15	ml
B	4	22	ml

Thank you Kindly!

 

[DHero]

Basically I want to return a value in column 2, but where the value in column 1 is equal to what I ak for.

How about: =IF(OR(table[dish]="A",table[dish]="B",table[dish]="C",table[dish]="D"),2,"")

I believe this fulfills what you have requested, but perhaps you have not been clear enough. There are hundreds of dishes and the number of Ingredients will vary for each. Somewhere else you wish to type, for example, dish "F" and the next cell you show "5" which would be the specific number of ingredients for it, is this it?

 

[bebo021999]

Ex365 can do that. But I donot have it.
BTW, is VBA accepted?

 

[Hanumanji]

How about: =IF(OR(table[dish]="A",table[dish]="B",table[dish]="C",table[dish]="D"),2,"")

I believe this fulfills what you have requested, but perhaps you have not been clear enough. There are hundreds of dishes and the number of Ingredients will vary for each. Somewhere else you wish to type, for example, dish "F" and the next cell you show "5" which would be the specific number of ingredients for it, is this it?

Thank you. Sorry, I'll clarify with this table - it was probably a mistake to put numbers as an example in ingredients

Dish	Ingredients	Qty	Unit
A	Brown Sugar	20	ml
A	Flour	20	ml
A	Milk	20	ml
A	Butter	20	ml
B	Brown Sugar	20	ml
B	Flour	20	ml
B	Cream	20	ml
B	Butter	20	ml

Example: Find & Return Brown Sugar where dish = A, or Find & Return Brown Sugar where dish = B (or any dish in a list that might be updated with time)

 

[Hanumanji]

Ex365 can do that. But I donot have it.
BTW, is VBA accepted?

Could you lead me on to what the solution would look like in Ex365? I can accept a VBA solution, but I'd prioritise a solution without

 

[bebo021999]

Could you lead me on to what the solution would look like in Ex365? I can accept a VBA solution, but I'd prioritise a solution without

I am not sure I can help with Ex365. Hope someone with Ex365 stop by and give help.

 

[StephenCrump]

Welcome to the Forum!

It's not at all clear from Posts #1 and #4 what results you are expecting.

But I am guessing:

	A	B	C	D
1	Dish	Ingredients	Qty	Unit
2	A	Brown Sugar	1	ml
3	A	Flour	2	ml
4	A	Milk	3	ml
5	A	Butter	4	ml
6	B	Brown Sugar	5	ml
7	B	Flour	6	ml
8	B	Cream	7	ml
9	B	Butter	8	ml
10
11
12	Brown Sugar
13	A	Brown Sugar	1	ml
14	B	Brown Sugar	5	ml
Sheet1

Cell Formulas
Range	Formula
A13:D14	A13	=FILTER(A2:D9,B2:B9=A12)
Dynamic array formulas.

If that's not right, please provide some examples.

 

[Hanumanji]

Welcome to the Forum!

It's not at all clear from Posts #1 and #4 what results you are expecting.

But I am guessing:

	A	B	C	D
1	Dish	Ingredients	Qty	Unit
2	A	Brown Sugar	1	ml
3	A	Flour	2	ml
4	A	Milk	3	ml
5	A	Butter	4	ml
6	B	Brown Sugar	5	ml
7	B	Flour	6	ml
8	B	Cream	7	ml
9	B	Butter	8	ml
10
11
12	Brown Sugar
13	A	Brown Sugar	1	ml
14	B	Brown Sugar	5	ml
Sheet1

Cell Formulas
Range	Formula
A13:D14	A13	=FILTER(A2:D9,B2:B9=A12)
Dynamic array formulas.

If that's not right, please provide some examples.

Thank you Sir!

I've experimented with the filter function, but not sure how to get the right dynamic out of it to use the resulting array in calculations.
See if this helps, this is the basic action I'd like to achieve.

Dish	Ingredients	Qty	Unit		Dish-A
Dish-A	Brown Sugar	40	ml			4	7	10
Dish-A	Butter	40	g		Brown Sugar
Dish-A	Flour	100	g		Butter
Dish-A	Milk	0.5	cups		Flour
Dish-B	Brown Sugar	50	ml		Milk
Dish-B	Butter	60	g
Dish-B	Cream	50	ml		Cell G3 will use F3 and F1 to retrieve the value of QTY and multiply it by G2
Dish-B	Flour	120	g

 

[StephenCrump]

That's a bit different to what you've described so far ....

Can you please post again using XL2BB, choosing Generate Output rather than Table Only Output, so we can see your cell references, and showing what's in cells F1, F3 and G2.

What results are you expecting? Do you want to multiply the recipe quantities by 4, 7 and 10?

 

[Hanumanji]

That's a bit different to what you've described so far ....

Can you please post again using XL2BB, choosing Generate Output rather than Table Only Output, so we can see your cell references, and showing what's in cells F1, F3 and G2.

What results are you expecting? Do you want to multiply the recipe quantities by 4, 7 and 10?

Thank you,

For me the main issue is finding the unique combination of the ingredient and dish I'm looking for.
If I was able to retrieve a specific Ingredient and it's location (coordinates) in the table for a specific Dish, I would be able to solve the rest.

But maybe there is a better solution? Here's an example of what I'm after with a fixed formula to solve G4 in the updated table. I need it to be dynamic though as the table and it's content might change over time.

Govindas - Recipe Book.xlsx
	A	B	C	D	E	F	G	H	I
1	Dish	Ingredients	Qty	Unit		Dish-A
2	Dish-A	Brown Sugar	40	ml			BATCHES
3	Dish-A	Butter	40	g		Ingredients	4	7	10
4	Dish-A	Flour	100	g		Brown Sugar	160ml
5	Dish-A	Milk	0.5	cups		Butter
6	Dish-B	Brown Sugar	50	ml		Flour
7	Dish-B	Butter	60	g		Milk
8	Dish-B	Cream	50	ml		Cell G3 will use F3 and F1 to retrieve the value of QTY and multiply it by G2
9	Dish-B	Flour	120	g
10	Dish-C	Carrots	10	kg
11	Dish-C	Cauliflower	5	kg
12	Dish-D	Parsley	4	bunches
13	Dish-D	Potatoes	20	kg
Sheet2

Cell Formulas
Range	Formula
G4	G4	=C2*G3 & D2