Database

[PatrickTT]

Hi,

I need to show how many suppliers there are of particular ingredients. Some ingredients may have one supplier or there may be two suppliers of a particular ingredient. See sample table below. What's the easiest way to do this? If I do a VLOOKUP it will only pull up one supplier for tomatoes (see table below).

I have a feeling I should be using Pivot...

Ingredient	Supplier
Tomatoes	Johnson & sons
Tomatoes	MD supplies
Oranges	MD supplies
Apples	Tom's Fresh fruit & Veg

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Ingredient	Supplier
2	Tomatoes	Johnson & sons			Tomatoes	2
3	Tomatoes	MD supplies
4	Oranges	MD supplies
5	Apples	Tom's Fresh fruit & Veg
6
Report

Cell Formulas
Range	Formula
F2	F2	=ROWS(UNIQUE(FILTER(B2:B100,A2:A100=E2)))

 

[PatrickTT]

Thanks, that works perfectly! Much obliged.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[PatrickTT]

If I need to sum the values of each ingredient, and some ingredients are sourced from multiple suppliers, what is the easiest way to do this?

Ingredient	Supplier	Volume
Tomatoes	Johnson & sons	200​
Tomatoes	MD supplies	150​
Oranges	MD supplies	10​
Apples	Tom's Fresh fruit & Veg	20​

 

[Fluff]

As this is a different question, it needs a new thread. Thanks

 

[PatrickTT]

Ah ok. Thanks will post now so.