MrDJShanahan
New Member
- Joined
- Aug 8, 2019
- Messages
- 6
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 |
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 |
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 | ||
but I still want to create a single list on the second sheet
Product | Qty |
Product A | 249 |
Product B | 197 |
Product C | 354 |
Any idea’s, because I have tried a few ways and I can’t getmy head around it.