three columns -> column, row, data in the intersections

[indigofaerie]

I have an Excel spreadsheet that is full of data similar to the following (space-hyphen-space indicates column break):

package - contents - quantity
V1 - 6 oz white chocolate SF - 6
V1 - 6 oz dark chocolate MI - 12
V1 - 8 oz milk chocolate SF - 8
V2 - 4 oz caramel chocolate MN - 4
V2 - 6 oz dark chocolate LA - 5
V3 - 4 oz milk chocolate SF - 4
V4 - 4 oz caramel chocolate MN - 5
V4 - 6 oz dark chocolate MI - 5
V4 - 8 oz milk chocolate SF - 2

(Note: this means that package V1 contains 6 of the 6 oz white chocolate SF, 12 of the 6 oz dark chocolate MI, and 8 of the 8 oz milk chocolate SF)

What I'd like to do is to create a spreadsheet that lists the package (V#) in a column, the contents (type of chocolate) as the rows, and the quantity as the intersection of the package and contents. I'd like to do this so that I can further manipulate the data to intersect with other information later (I have a list of customers who have purchased different packages and want to turn it into a list of customers who own which types of chocolate and how many types. So if I don't need to take the step I'm taking now and can accomplish this with a direct merge with my customer purchasing data, please tell me!!!)

I would just go ahead and retype the information in this format, but I have a total of 229 versions of packages and 252 types of chocolate (because the descriptor ends up containing bar size and where it was made) so obviously it would be very complicated to retype this. A friend who knows Excel much better than I do has already wrestled with SUMIF, COUNT, IF, and some combinations of those. (I've already CONCATENATED a previous three columns to come up with one column of type of chocolate/bar/where it was made to be one column instead). My friend gave up working on it after struggling with it for about 30 minutes.

Any suggestions?

In advance, thanks so much!! I'm willing to provide any other information necessary if you have questions. Thank you!!!

 

[acw]

Hi

This assumes that the raw data is in sheet2 and the output is in sheet3. Adjust the ranges to suit.

Sub ddd()

Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"F1"), Unique:=True
Range("b1:b10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"g1"), Unique:=True
Range("g1:g50").Copy Destination:=Sheets("sheet3").Range("a1")

Range("f2:f50").Copy
Sheets("sheet3").Activate
Range("b1").Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Transpose:=True

Range("b2:e7").Formula = "=SUMPRODUCT(--(Sheet2!$B$2:$B$10=Sheet3!$A2),--(Sheet2!$A$2:$A$10=Sheet3!B$1),(Sheet2!$C$2:$C$10))"

End Sub


Tony