Transposing multiple column data into rows

Hi guys,

First time posting, long time lurker.

I've tried doing this through pivots and looked through a bunch of threads but I can't seem to find a solution and was hoping someone could help me out.

I have a set of items with multiple subtypes and each subtype has specific product IDs assigned to them with percentage of sales on them.

Product Name Subtype Name PID % of sales credit
Product1 Subtype1 PID1 0.2
Product1 Subtype1 PID2 0.3
Product1 Subtype1 PID3 0.3
Product1 Subtype2 PID4 0.1
Product1 Subtype2 PID5 0.1
Product 2 Subtype3 PID6 0.2
Product 2 Subtype3 PID7 0.3
Product 2 Subtype4 PID8 0.2
Product 2 Subtype4 PID9 0.3
Product3 Subtype5 PID10 0.15
Product3 Subtype5 PID11 0.15
Product3 Subtype5 PID12 0.2
Product3 Subtype6 PID13 0.15
Product3 Subtype6 PID14 0.2
Product3 Subtype6 PID15 0.15


I'm trying to consolidate the information a more compressed version, transposing the multiple columns into a single row for each item-subtype.

Product Name Subtype Name PID % of sales credit PID % of sales credit PID % of sales credit
Product 1 Subtype1 PID1 0.2 PID2 0.3 PID3 0.3
Product1 Subtype2 PID4 0.1 PID5 0.1
Product 2 Subtype3 PID6 0.2 PID7 0.3
Product 2 Subtype4 PID8 0.2 PID9 0.3
Product3 Subtype5 PID10 0.15 PID11 0.15 PID12 0.2
Product3 Subtype6 PID13 0.15 PID14 0.2 PID15 0.15

So I guess I solved my own issue.

what I did was concatenate the product name and subtype labeled, NewName. Then I added a simple IF formula to each duplicate value and then concatenate that to the newly created NewName.

Then I created a tabular table removing the totals and listing all duplicates. Given the highest number of duplicates, I added the equivalent number of repeating VLOOKUP formulas to the end of the tabular table referencing the NewName and numerical value.

Sorry if I didn't make sense but thats how I went about solving this issue. I'll post something up later with examples to show how I did it.

The attached file shows how you can do this with formulae. Let me know if you need an explanation of how it works.

Hope this helps.

Pete

Thank you all. They were all great solutions to my problem.