Reformat a vertical data set to a horizontal table

**zenmeta4** · 06-03-2009, 02:50 PM

I have a spreadsheet that I need to reformat into a more usable format. The spreadsheet shows each employee #, then the type of deduction, then the amount. Currently, the data is in rows and each employee number is listed multiple times. I would like to reformat it so that each employee number is on one row, with amounts being in separate columns.

I have attached a sample (this is not real data, my actual spreadsheet has 6,800 rows).

I haven't had any luck using the TRANSPOSE option, and when I do a Pivot table, Excel wants to analyze the data (ie- sum, count, etc), rather than show the actual data. There is probably an easy solution, but I have not been able to find it.

THANK YOU TO ANYONE WHO CAN HELP.

**DonkeyOte** · 06-04-2009, 03:55 AM

I would advise INDEX approach, eg to replicate Sheet1!B5:D16 in your preferred format on Sheet2

A2: =INDEX(Sheet1!$B$5:$B$16,1+(3*(ROWS(A$2:A2)-1)))
copy down to A5

B2: =INDEX(Sheet1!$D$5:$D$16,COLUMNS($B2:B2)+(3*(ROWS(B$2:B2)-1)))
copy across matrix B2:D5

Reformat a vertical data set to a horizontal table

LinkBack

Thread Tools

Rate This Thread

Display

Reformat a vertical data set to a horizontal table

Re: Reformat a vertical data set to a horizontal table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions