Database query

[PatrickTT]

Hi, I'm looking to extract the supplier info from the below sample table.

Ingredient	Supplier
Apples	Schroeders
Oranges	McCarthys
Apples	Peters
Tomatoes	McCabes
Oranges	Thomassons
Oranges	Hendersons

I would like to format the info as per the below:
Is there a formula for this?

Ingredient	Supplier 1	Supplier 2	Supplier 3
Apples	Schroeders	Peters
Oranges	McCarthys	Thomassons	Hendersons
Tomatoes	McCabes

 

[Fluff]

Do you have the new functions such as VSTACK yet?

 

[PatrickTT]

No, not available to me yet.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Ingredient	Supplier
2	Apples	Schroeders		Apples	Schroeders	Peters
3	Oranges	McCarthys		Oranges	McCarthys	Thomassons	Hendersons
4	Apples	Peters		Tomatoes	McCabes
5	Tomatoes	McCabes
6	Oranges	Thomassons
7	Oranges	Hendersons
8
Data

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A7)
E2:F2,E4,E3:G3	E2	=TRANSPOSE(FILTER(B2:B7,A2:A7=D2))
Dynamic array formulas.

 

[PatrickTT]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Ingredient	Supplier
2	Apples	Schroeders		Apples	Schroeders	Peters
3	Oranges	McCarthys		Oranges	McCarthys	Thomassons	Hendersons
4	Apples	Peters		Tomatoes	McCabes
5	Tomatoes	McCabes
6	Oranges	Thomassons
7	Oranges	Hendersons
8
Data

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A7)
E2:F2,E4,E3:G3	E2	=TRANSPOSE(FILTER(B2:B7,A2:A7=D2))
Dynamic array formulas.

Hi, this works perfectly apart from one problem.
If ingredients are repeated and are sourced from the same file, then the formula also repeats them. See below:

Ingredient	Supplier
Apples	Schroeders		Ingredient	Supplier 1	Supplier 2	Supplier 3	Supplier 4
Oranges	McCarthys		Apples	Schroeders	Peters	Schroeders
Apples	Peters		Oranges	McCarthys	Thomassons	Hendersons	Thomassons
Tomatoes	McCabes		Tomatoes	McCabes
Oranges	Thomassons
Oranges	Hendersons
Oranges	Thomassons
Apples	Schroeders

 

[Fluff]

Ok, how about

=TRANSPOSE(unique(FILTER(B2:B7,A2:A7=D2)))

 

[PatrickTT]

Perfect, thank you very much!

 

[Fluff]

Glad to help & thanks for the feedback.