CODEWITHSUNDEEP
excelvbarowstranspose
user257069
user257069

Reputation: 19

how to use a macro to transpose data from rows into columns

I am totally new to VBA (aside from recording and running manually) so was hoping somebody could help me with designing this. I have a worksheet with unspecified # of rows per unique ID (column A), based on date and type. :

A        B             C            D

1    12Jan2010    type A     Person1

1    16Jan2010    type B     Person1

2    06Dec2009    type A     Person2 

3    16Dec2009    type A     Person1

5    20Dec2009    type A     Person2

5    02Jan2010    type B     Person2
4    10Dec2009    type A     Person2

based on the unique ID, I would like to transpose this data into 1 row per unique ID, and then have all the date values put in chronological order across the row so it looks like this:

A   B           C                D
1   Person1     12Jan2010(A)     16Jan2010(B)  
2   Person2     06Dec2009(A)  
3   Person1     16Dec2009(A)  
4   Person2     10Dec2009(A)  
5   Person2     20Dec2009(A)     02Jan2010(B)

Is there a way I can have this done in a macro?

Upvotes: 1

Views: 2942

Answers (1)

vicatcu
vicatcu

Reputation: 5837

Use a Pivot Table! Select the whole table, and create a pivot table with the side field being column A, the top field being column B and just do count of column A in the middle aggregate section.

You'll end up with a matrix of unique id's as rows, dates as columns, and counts of rows at intersections!

Upvotes: 1

Related Questions