What is the best way to create a kit list

[MrDJShanahan]

Hi


I have 2 sheets in a workbook.


The first sheet ‘kit list’ has a table with a drop down boxin B12 to B210, so I can select a product from the drop down, then in C12 to C210,I can put the number of each of the products I want. There could be multiplevalues that are the same in column B.

Site	Product	Qty
Moor Street	Product A	10
Moor Street	Product B	40
Moor Street	Product C	32
John Street	Product A	16
Blue Street	Product A	8
Blue Street	Product C	1
Green Street	Product B	14
Green Street	Product C	82
Orange Street	Product C	34
Orange Street	Product A	14

<tbody> </tbody>

In the second sheet ’bill of materials’ I want to then createa list from the information from the first sheet listing all the unique valuesand a count of the number of these value.

Product	Qty
Product A	48
Product B	54
Product C	149

<tbody> </tbody>

Now imagine if there are multiple columns in the first sheetwith different floors

Floor1	Floor2	Floor3
Site	Product	Qty	Site	Product	Qty	Site	Product	Qty
Moor Street	Product A	10	Moor Street	Product A	12	Moor Street	Product A	9
Moor Street	Product B	40	Moor Street	Product B	19	Moor Street	Product B	1
Moor Street	Product C	32	Moor Street	Product C	22	Moor Street	Product C	17
John Street	Product A	16	John Street	Product A	46	John Street	Product A	85
Blue Street	Product A	8	Blue Street	Product A	9	Blue Street	Product A	22
Blue Street	Product C	1	Blue Street	Product C	1	Blue Street	Product C	10
Green Street	Product B	14	Green Street	Product B	17	Green Street	Product B	40
Green Street	Product C	82	Green Street	Product C	85	Green Street	Product C	32
Orange Street	Product C	34	Orange Street	Product C	22	Orange Street	Product C	16
Orange Street	Product A	14	Orange Street	Product A	10	Orange Street	Product A	8

<tbody> </tbody>

but I still want to create a single list on the second sheet

Product	Qty
Product A	249
Product B	197
Product C	354

<tbody> </tbody>

Any idea’s, because I have tried a few ways and I can’t getmy head around it.

 

[Misca]

The best way would be to get rid of the current data structure where you have the same data multiple times in the same row. A much better way would be to add the Floor info to a new column and just have all the Sites in a single Site -column etc. This is the data structure Excel - and just about any other app - is programmed to use.

This way it's easy to slice and dice your data anyway you could possibly want to analyze it.

If that is not an option I'd use Power Query to unpivot the badly structured data and either restructure into the right structured table and use a pivot table to get my Product Qtys or simply use the PQ all the way to return the desired end result.