Transposing columns to rows with pivot table?

**cooter_slackjaw** · 03-09-2009, 04:06 PM

Hi,

I need to transpose some data from columns to rows, it is not easy to explain in words what I want do so I have put an example below or see attached file:

The data is currently in the following format(two headers are col. A = Site and col. B = Cell ID):

SITE - Cell ID
312P0011A - 50112
312P0011A - 50151
312P0011A - 50052
312P0011A - 50153
312P0011A - 51881
312P0011A - 50152
312P0052A - 10411
312P0052A - 51881
312P0052A - 51673
312P0052A - 51672
312P0122A - 50052
312P0122A - 51803
312P0122A - 51223
312P0122A - 51222

The output I want is as follows:

SITE Cell ID
312P0011A 50112 50151 50052 50153 51881 50152
312P0052A 10411 51881 51673 51672
312P0122A 50052 51803 51223 51222

So basically I have compiled a row for each of the entries in column A (Sites).

I need to do this on a lot of data and wondered if there is a quick way of doing this, it seems to be a mix of transposing the data and pivot table, but no matter what I try I can't get the right results.

Any help would be greatly appreciated.
Thanks!

**Mikeopolo** · 04-01-2009, 12:23 AM

Try the attached worksheet
Regards
Mike

**Duncan Richards** · 09-16-2011, 12:51 PM

Mike,

You are a star!!

Thanks so much. This saved me so much wasted time with an array that I was using between spreadsheets that would take forever to calculate.
If you are ever over on Vancouver Island, I will buy you a large frosty beverage.

Many thanks!!!!

**Mikeopolo** · 09-16-2011, 05:18 PM

Hello Duncan, thanks for your note, very much appreciated. I've been able to visit several Pacific islands and Los Angeles/San Francisco in the course of my work over the years, but so far not Canada. One never knows.

Regards
Mike