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.

<tbody> [TD="width: 200, bgcolor: transparent"] Site [/TD]
[TD="width: 200, bgcolor: transparent"] Product [/TD]
[TD="width: 200, bgcolor: transparent"] Qty [/TD]
[TD="width: 200, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product A [/TD]
[TD="width: 200, bgcolor: transparent"] 10 [/TD]
[TD="width: 200, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product B [/TD]
[TD="width: 200, bgcolor: transparent"] 40 [/TD]
[TD="width: 200, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product C [/TD]
[TD="width: 200, bgcolor: transparent"] 32 [/TD]
[TD="width: 200, bgcolor: transparent"] John Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product A [/TD]
[TD="width: 200, bgcolor: transparent"] 16 [/TD]
[TD="width: 200, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product A [/TD]
[TD="width: 200, bgcolor: transparent"] 8 [/TD]
[TD="width: 200, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product C [/TD]
[TD="width: 200, bgcolor: transparent"] 1 [/TD]
[TD="width: 200, bgcolor: transparent"] Green Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product B [/TD]
[TD="width: 200, bgcolor: transparent"] 14 [/TD]
[TD="width: 200, bgcolor: transparent"] Green Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product C [/TD]
[TD="width: 200, bgcolor: transparent"] 82 [/TD]
[TD="width: 200, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product C [/TD]
[TD="width: 200, bgcolor: transparent"] 34 [/TD]
[TD="width: 200, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 200, bgcolor: transparent"] Product A [/TD]
[TD="width: 200, bgcolor: transparent"] 14 [/TD]
</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.

<tbody> [TD="width: 200, bgcolor: transparent"] Product [/TD]
[TD="width: 200, bgcolor: transparent"] Qty [/TD]
[TD="width: 200, bgcolor: transparent"] Product A [/TD]
[TD="width: 200, bgcolor: transparent"] 48 [/TD]
[TD="width: 200, bgcolor: transparent"] Product B [/TD]
[TD="width: 200, bgcolor: transparent"] 54 [/TD]
[TD="width: 200, bgcolor: transparent"] Product C [/TD]
[TD="width: 200, bgcolor: transparent"] 149 [/TD]
</tbody>

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

<tbody> [TD="width: 181, bgcolor: transparent, colspan: 3"] Floor1 [/TD]
[TD="width: 181, bgcolor: transparent, colspan: 3"] Floor2 [/TD]
[TD="width: 176, bgcolor: transparent, colspan: 5"] Floor3 [/TD]
[TD="width: 85, bgcolor: transparent"] Site [/TD]
[TD="width: 66, bgcolor: transparent"] Product [/TD]
[TD="width: 30, bgcolor: transparent"] Qty [/TD]
[TD="width: 85, bgcolor: transparent"] Site [/TD]
[TD="width: 66, bgcolor: transparent"] Product [/TD]
[TD="width: 30, bgcolor: transparent"] Qty [/TD]
[TD="width: 80, bgcolor: transparent, colspan: 2"] Site [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product [/TD]
[TD="width: 30, bgcolor: transparent"] Qty [/TD]
[TD="width: 85, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 10 [/TD]
[TD="width: 85, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 12 [/TD]
[TD="width: 76, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product A [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 9 [/TD]
[TD="width: 85, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product B [/TD]
[TD="width: 30, bgcolor: transparent"] 40 [/TD]
[TD="width: 85, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product B [/TD]
[TD="width: 30, bgcolor: transparent"] 19 [/TD]
[TD="width: 76, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product B [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 1 [/TD]
[TD="width: 85, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 32 [/TD]
[TD="width: 85, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 22 [/TD]
[TD="width: 76, bgcolor: transparent"] Moor Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product C [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 17 [/TD]
[TD="width: 85, bgcolor: transparent"] John Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 16 [/TD]
[TD="width: 85, bgcolor: transparent"] John Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 46 [/TD]
[TD="width: 76, bgcolor: transparent"] John Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product A [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 85 [/TD]
[TD="width: 85, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 8 [/TD]
[TD="width: 85, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 9 [/TD]
[TD="width: 76, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product A [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 22 [/TD]
[TD="width: 85, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 1 [/TD]
[TD="width: 85, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 1 [/TD]
[TD="width: 76, bgcolor: transparent"] Blue Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product C [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 10 [/TD]
[TD="width: 85, bgcolor: transparent"] Green Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product B [/TD]
[TD="width: 30, bgcolor: transparent"] 14 [/TD]
[TD="width: 85, bgcolor: transparent"] Green Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product B [/TD]
[TD="width: 30, bgcolor: transparent"] 17 [/TD]
[TD="width: 76, bgcolor: transparent"] Green Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product B [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 40 [/TD]
[TD="width: 85, bgcolor: transparent"] Green Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 82 [/TD]
[TD="width: 85, bgcolor: transparent"] Green Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 85 [/TD]
[TD="width: 76, bgcolor: transparent"] Green Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product C [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 32 [/TD]
[TD="width: 85, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 34 [/TD]
[TD="width: 85, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product C [/TD]
[TD="width: 30, bgcolor: transparent"] 22 [/TD]
[TD="width: 76, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product C [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 16 [/TD]
[TD="width: 85, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 14 [/TD]
[TD="width: 85, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 66, bgcolor: transparent"] Product A [/TD]
[TD="width: 30, bgcolor: transparent"] 10 [/TD]
[TD="width: 76, bgcolor: transparent"] Orange Street [/TD]
[TD="width: 66, bgcolor: transparent, colspan: 2"] Product A [/TD]
[TD="width: 34, bgcolor: transparent, colspan: 2"] 8 [/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 30, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 30, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"][/TD]
[TD="width: 4, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 4, bgcolor: transparent"][/TD]
[TD="width: 30, bgcolor: transparent"][/TD]
</tbody>

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

<tbody> [TD="width: 200, bgcolor: transparent"] Product [/TD]
[TD="width: 200, bgcolor: transparent"] Qty [/TD]
[TD="width: 200, bgcolor: transparent"] Product A [/TD]
[TD="width: 200, bgcolor: transparent"] 249 [/TD]
[TD="width: 200, bgcolor: transparent"] Product B [/TD]
[TD="width: 200, bgcolor: transparent"] 197 [/TD]
[TD="width: 200, bgcolor: transparent"] Product C [/TD]
[TD="width: 200, bgcolor: transparent"] 354 [/TD]
</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.