Hi there,
I am trying to tame a massive spreadsheet I inherited in to something usable. Through some hack and slash macros, lookups and beer, I've got it to a point I'm stumped. Essentially, it's a table of components with price and quantity breakdowns but as it's formatted it can't be used easily. If I can format it better I can use a simple pivot table to look up parts and prices based on quantity.
Here is a representation what I've got (the real file is about 3000 rows and 100 or so columns):
I'd like to turn it in to this:
I have it in this format:
Looking at the logic, I somehow need to evaluate "if part is 5683 Rev B AND price is $23.00" then paste "Qty 5" in to C2. It seems like it should be easy to cross reference Part and Price then return the column header but I'm lost. I've played with index(match) and other techniques but nothing has worked. There is no set pattern in the quantity / price relationship so it needs to be done off their values.
I'm sure I'm missing a simple solution but I just can't see it!
Thank you much!
I am trying to tame a massive spreadsheet I inherited in to something usable. Through some hack and slash macros, lookups and beer, I've got it to a point I'm stumped. Essentially, it's a table of components with price and quantity breakdowns but as it's formatted it can't be used easily. If I can format it better I can use a simple pivot table to look up parts and prices based on quantity.
Here is a representation what I've got (the real file is about 3000 rows and 100 or so columns):
Part | Qty 1 | Qty 5 | Qty 12 | Qty 18 | Qty 24 |
5683 RevB | $23.00 | ||||
4984 Rev A | $56.00 | $42.00 | $37.00 | ||
9963 Rev C | $98.00 | ||||
1366 Rev A | $115.00 | $105.00 | |||
I'd like to turn it in to this:
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
12 | Part | Price | Quantity | ||
13 | 5683 RevB | $23.00 | Qty 5 | ||
14 | 4984 Rev A | $56.00 | Qty 1 | ||
15 | 4984 Rev A | $42.00 | Qty 12 | ||
16 | 4984 Rev A | $37.00 | Qty 24 | ||
17 | 9963 Rev C | $98.00 | Qty 18 | ||
18 | 1366 Rev A | $115.00 | Qty 5 | ||
19 | 1366 Rev A | $105.00 | Qty 24 | ||
Sheet1 |
I have it in this format:
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Part | Price | Quantity | Part | Qty 1 | Qty 5 | Qty 12 | Qty 18 | Qty 24 | |||
2 | 5683 RevB | $23.00 | 5683 RevB | $23.00 | ||||||||
3 | 4984 Rev A | $56.00 | 4984 Rev A | $56.00 | $42.00 | $37.00 | ||||||
4 | 4984 Rev A | $42.00 | 9963 Rev C | $98.00 | ||||||||
5 | 4984 Rev A | $37.00 | 1366 Rev A | $115.00 | $105.00 | |||||||
6 | 9963 Rev C | $98.00 | ||||||||||
7 | 1366 Rev A | $115.00 | ||||||||||
8 | 1366 Rev A | $105.00 | ||||||||||
Sheet1 |
Looking at the logic, I somehow need to evaluate "if part is 5683 Rev B AND price is $23.00" then paste "Qty 5" in to C2. It seems like it should be easy to cross reference Part and Price then return the column header but I'm lost. I've played with index(match) and other techniques but nothing has worked. There is no set pattern in the quantity / price relationship so it needs to be done off their values.
I'm sure I'm missing a simple solution but I just can't see it!
Thank you much!