Moving data from columns to rows

**jodes78** · 10-30-2012, 08:08 PM

Hi all,

I'm hoping someone will have the answer to this question. I have a spreadsheet with over 8000 rows of data. One of the column headings is Unit and the other is Result. Obviously all the data runs vertically down the sheet. I need to take these columns and somehow move them into a new spreadsheet HORIZONTALLY in rows not columns...

Is there anyway of doing this???

example - Current view

Name Unit Result
Joe 1 Pass
Joe 2 Fail
Joe 3 Pass

I need it to look like this

Name Unit Result Unit Result Unit Result etc
Joe 1 Pass 2 Fail 3 Pass

Looking forward to hearing of any solutions to this problem~!

Thanks,
Jodie

**Pete_UK** · 10-30-2012, 08:50 PM

It's getting a bit late here, so I can't devote the time to fully answer your problem, but have a look at post #7 in this thread from earlier:

http://www.excelforum.com/excel-gene...readsheet.html

It does a similar job to what you want (though I expect you would want to match on the name and could have a variable number of unit/result pairs).

Anyway, it might give you some ideas.

Hope this helps.

Pete

**JBeaucaire** · 10-30-2012, 09:02 PM

'COLUMNS TO ROWS
Here's a macro for merging columns of data to one row matching for column A. There's a sample workbook too you could drop your data into and test it out.

Columns to Rows - Part 2

The macro is called CONSOLIDATE and on sheet Part 2, if you drop your 3 columns of data into that sheet and run the macro you'll get the exact result you've stated.

**jodes78** · 10-31-2012, 07:01 PM

Hi Pete and Jerry,

Thank you for your replies! I have tested your formula Pete and can't seem to get it to work... (keep in mind that I'm not great with Excel!) Jerry, I wasn't sure how to add and run the macro data from your link, sorry!

I have taken a snapshot of the spreadsheet I'm working on and the column headings that I need the data to move into. Hope this might give you a better idea of what I'm trying to achieve.ExcelCapture.PNG

As you will see each student completes 15 or 16 units (with associated results). I need to move this data from the columns into one row for each student.

thanks again, I really appreciate your help!