What is the best way to create a kit list

MrDJShanahan

New Member
Joined
Aug 8, 2019
Messages
4

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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,573
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,222
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top