Merging Rows of Data

[Monsignor]

I'm wondering if this is really a Power Pivot question rather than a pure Excel question. I've installed Power Pivot but never used it. Here's the challenge:


There are 11,000 rows of which 4,000 are unique.
Each entity shows up 1 to 15 times depending on how many orders they've placed.

The goal is to get each person on their own row with all of their orders.

Tom Ohio Sweater
Tom Ohio Glasses Socks
Les Florida Glasses
Ruth Ohio Socks Hat Wrench
Ruth Ohio Desk Pens Socks
Ruth Ohio Glasses


A good result would be:

Tom Ohio Sweater Glasses Socks
Les Florida Glasses
Ruth Ohio Socks Hat Wrench Desk Pens Socks Glasses

The ultimate result:

Tom Ohio Sweater Glasses Socks
Les Florida Glasses
Ruth Ohio Glasses Socks Hat Wrench Pens Desk


Thanks for any and all help. Even if there's a solution that takes a few steps, it's much appreciated.

 

[Monsignor]

I just noticed that the spaces were removed from my example. I was trying to show that the ultimate goal would be to have a column for each item. In the example of Les, the column after his state would be empty because he hasn't purchased a Sweater. And that would be the same for Ruth.

 

[masplin]

So woudl you want a table like this?

Name State Sweater Glasses Socks Hat etc
Tom Ohio Yes Yes Yes No

Or numbers of orders

Tom Ohio 3 6 2 0

Are your data table columns?

Name State Item
Tom Ohio Sweater

Mike

 

[Monsignor]

Thanks Masplin

Ultimately the data set would be in different columns and look like:

Name-----State----Sweater----Glasses ...
Tom-------Ohio----Sweater----Glasses

(using the dashes to simulate columns)

 

[masplin]

So in the "Sweater" column you want it to say "Sweater" not "Yes" or count of sweater purchases? Just wondered as easier to work with later 2 if you want to do somethnig else with the data.

 

[masplin]

Ok this might not be the most elegant solution but it works.

1. You need to create a a table of your unique customers with their states. Assuming your data table is A1:C200000 and is called "Data". First select all of A:A (names field) and name it as "List". Then in E2={IFERROR(INDEX(List,MATCH(0,COUNTIF($E$1:E1,List),0)),"")} but do ctrl-shift-enter to make an array. Put "name" in E1. In F2 put =IFERROR(VLOOKUP(E2,Data[#All],2,FALSE),"") so looks up the state for each name. Put "State" in F1. Select all the names/states in E:F ad format as a table called "Output"
2. Now link both "Data" and "output" tables to powerpivot using "create linked table"
3. On Output tab you can now calculate how many of each item type the unique user in "output" has bought. Add a new column on "output" next to "State" with this formula =if(CALCULATE(countrows(data),filter(data,Output[Name]=Data[Name] && Data[Item]="Sweater"))>0,"Sweater",blank()). Rename the column "sweater"
4. repeat with columns for each item type changing the formula above.
5. You may be happy just to copy and paste this form powerpivot to an excel sheet, but there is a more complex way to do it automatically.

I have made a very small version of this and I can email it to you if you send me a PM with your email address.

Hope that hleps

Mike