Selectively transposing data in Excel

[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.