Counting cells for each supplier

[tomvangelder12]

Dear all,

I'm working on a data set which consists of suppliers, the differentproduct they sell and the amount of money corresponding to each productgroup.
My goal:
Count the amount of different productgroups each supplier has.

In Column A you can find both the suppliers and productgroups. Bold is the supplier, not bold are the product groups.
In column B you can find the amount of money a supplier has supplied, and the amount of money each productgroup consists of. the sum of the productgroups, below 1 supplier, is equal to the amount of money corresponding to the supplier.
column C should show the amount of productgroups next to each supplier.

The full data set consists of about 3000 cells, which makes me want to automate this.

Im looking for a formule which I could place in cell C3 and then pull down all the way to the end showing the amount of productgroups next to each supplier
The amount of productgroups corresponding to the suppliers is completely random and can vary between 1 to approximately 500.

Thanks for the help already!

How it should look:

[TABLE="width: 394"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Supplier 1[/TD]
[TD] € 588,29[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD] Snacks[/TD]
[TD] € 588,29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier 2[/TD]
[TD] € 96,52[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] Sodas[/TD]
[TD] € 74,80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Tea[/TD]
[TD] € 21,72[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supplier 3[/TD]
[TD] € 561,94[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD] Cleaning stuff[/TD]
[TD] € 309,63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Cooling components[/TD]
[TD] € 189,14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Kitched stuff[/TD]
[TD] € 51,45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Office supplies[/TD]
[TD] € 8,77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Office supplies 2[/TD]
[TD] € 2,95[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[123rickfear]

do you have a list of all suppliers? or can they change at any time?

 

[Snakehips]

tomvangelder12,

Unless you have some means of differentiating between Supplier and Product in column A other than Supplier being bold then you are going to struggle to do this with formula.

I can give you a simple bit of vba that will do the job based on Supplier being bold if you are ok to use vba?

If you have a full list of suppliers that could be used to verify by way of a lookup that text in A is a a supplier then you could use formulas.?

 

[tomvangelder12]

Thanks for the replies.
The data is generated from a pivot table. This means I could also make a list of all the suppliers.

The original data consists of each product bought from a supplier as a total. So for example all the COCA-COLA cans bought from COCA COLA Enterprises Netherlands is filling 1 row.


As an example coca cola cans(the data is fiction)

[TABLE="width: 1408"]
<tbody>[TR]
[TD]article nr[/TD]
[TD]IVP[/TD]
[TD]VP[/TD]
[TD]size[/TD]
[TD]Unit[/TD]
[TD]Article description[/TD]
[TD]product group nr.[/TD]
[TD]product group[/TD]
[TD]supplier[/TD]
[TD]EAN CE[/TD]
[TD]EAN HE[/TD]
[TD]amount
( colli/kg )[/TD]
[TD]revenue
( euro )[/TD]
[/TR]
[TR]
[TD]1111111[/TD]
[TD]24[/TD]
[TD]BL[/TD]
[TD]33,000[/TD]
[TD]CL[/TD]
[TD]COCA-COLA REGULAR, BLIK[/TD]
[TD]121[/TD]
[TD]Soft drinks[/TD]
[TD]COCA COLA ENTERPRISES NED BV[/TD]
[TD][/TD]
[TD][/TD]
[TD]10 000[/TD]
[TD]100 000[/TD]
[/TR]
</tbody>[/TABLE]

 

[tomvangelder12]

Btw: the data is from 2015. So it doesn't change anymore

 

[tomvangelder12]

tomvangelder12,

Unless you have some means of differentiating between Supplier and Product in column A other than Supplier being bold then you are going to struggle to do this with formula.

I can give you a simple bit of vba that will do the job based on Supplier being bold if you are ok to use vba?

If you have a full list of suppliers that could be used to verify by way of a lookup that text in A is a a supplier then you could use formulas.?

Hey Snakehips, could u assist with the formula? I do have a full list of suppliers that could be used as a lookup text.

 

[Snakehips]

Can you not use a pivot table to do the counting?