Selectively transposing data in Excel

Posted by Betsy on November 12, 2013 in Uncategorized |

[Update, 11/13/2013: Thanks to Facebook comments about this post, I learned about pivot tables. (Thanks, Scott!) This is a far quicker and easier way to do the same thing as the macro linked below. I used this tutorial and got up to speed in no time.]


I wish I had thought about looking for a macro to do this a lot sooner.  I often have data with a row representing each item for each participant like this:

Participant ID Item number Response
1 1 1
1 2 0
1 3 1
1 4 0
2 1 0
2 2 1
2 3 0
2 4 1

But for analysis, I need to get it into a shape with a single row for each participant, like this:

Participant ID Item 1 Item 2 Item 3 Item 4
 1  1 0 1 0
 2  0 1 0 1

Rather than copying and pasting/transposing all of the data by hand (and sometimes it’s A LOT of data), today I found an Excel macro to do this. (Thanks, internetz!)

I may never do anything by hand ever again.


Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2008-2018 Betsy's Eclection All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.