Re-arrange a worksheet range to a pivot-friendly table?

**Alabaster** · 11-30-2011, 11:09 AM

[QUESTION IS REPEATED IN MORE DETAIL IN THE ATTACHED SAMPLE SPREADSHEET]

Hello,

I have an Excel worksheet which is exported from a Google Docs Form. (Users fill out the form and it populates the spreadsheet, which I then export to Excel).

It is formatted like so (and cannot be changed):

headers: TIMESTAMP | ITEM | NAME1 | NAME2 | NAME3 | etc.
So each row looks like:

11/30/2011 8:30AM | Item A | 25 | 46 | 74
(each number is a score for Item A corresponding to Name 1, Name 2, Name 3, etc.)

I would like to re-arrange the data into a table that is more appropriate for pivot-tables:

11/30/2011 8:30AM | Item A | Name1 | 25
11/30/2011 8:30AM | Item A | Name2 | 46
11/30/2011 8:30AM | Item A | Name3 | 74

I will be doing this daily, as new rows are continually added to the source table -- so I need it to be simple and quick to maintain (once I put in the initial labor of setting up the system). .

Is there any way to do such a thing either automatically (referencing the data source directly?
If not, is there a way to do it manually (with formulas) in as few clicks as possible? (e.g. by pasting some rows of the source data into a "helper worksheet," which uses some index/match/lookup voodoo to display the data in the desired format, and then Paste-Values the re-arranged data into my destination table?
If not, is VBS my best option? I'm fairly competent with Excel formulas but I'm inexperienced with VBS so I'd need some more detailed guidance on this.

Thanks

**JBeaucaire** · 11-30-2011, 11:52 AM

Put the data table in cell A1 and run the attached macro:

Please Login or Register  to view this content.

**ChemistB** · 11-30-2011, 11:53 AM

Okay, it's a little complex but once you've done it a few times it is easy. I'm guessing a VBA answer might be simplier but here is how I do it.

Okay, here's how to do it.....
1. You need to move the Pivot Table Wizard to your Quick access toolbar
1a. Excel Options > Customize> All Commands > PivotTable and Pivot Tablechart Wizard
1b. Add> OK

2. Insert a column before your names (Col D). Dragged down use the formula
=B4&"@" &C4

3. Click on the PivotTable Wizard Icon.
3a. Select "Multiple Consolidation Ranges">Next
3b. Select "I will create the page fields" > Next
3c. Select your range from the new column to the right including headers (D3:I6) > Add > Finish
4. A pivot table appears in new sheet (Sheet 6). Unclick "Row" and "Column" in your pivot table field list. Close field list
At this point, you have a very small pivot table (1 cell)
5. Double click on the value in the cell. This will create a normalized list. (Sheet 8)
6. Insert two columns Before B. In the new B
=LEFT(Table3[[#This Row],[Row]],FIND("@",A2)-1)+0
In C
=MID(Table3[[#This Row],[Row]],FIND("@",A2)+1,50) (See Sheet 7)
7. Modify the headers , Copy and Paste special Values to remove formulas and remove Column A. Then use this new table to create a Pivot Table.
Example attached.
Did that work?

**Alabaster** · 11-30-2011, 03:48 PM

Thanks to both of you for your answers -- I will try both out and see which suits my application best.

In the meantime, I came up with my own solution using formulas and several "helper" cells. It's not pretty but it does exactly what I need -- and it's flexible and automatic. I have attached the file here in case it might be useful to someone searching this forum for a similar question. This solution does require me to copy and paste (values) the data into my destination table, so it's not a perfect solution yet, but it only takes a few seconds, so I can't complain.

If any Excel gurus here can suggest any refinements, I'd be happy to hear them.

**ChemistB** · 12-01-2011, 09:19 AM

Alabaster,
I think your solution is very good. I'm not sure there's much room for reduction unless one moves to array formulas but then your system resources are slowed down. Nice job.