Transpose large data set

[jamezsez]

Hi All,

I am having much difficulty transposing a large data set. The result I am after seems simple but is proving to be somewhat difficult.

I have a list of the following data.

[TABLE="width: 200"]
<tbody>[TR]
[TD]Account1
[/TD]
[/TR]
[TR]
[TD]a
[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 200"]
<tbody>[TR]
[TD]Account2[/TD]
[/TR]
[TR]
[TD]d[/TD]
[/TR]
[TR]
[TD]e[/TD]
[/TR]
[TR]
[TD]f[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 200"]
<tbody>[TR]
[TD]Account3[/TD]
[/TR]
[TR]
[TD]g[/TD]
[/TR]
[TR]
[TD]h[/TD]
[/TR]
[TR]
[TD]i[/TD]
[/TR]
</tbody>[/TABLE]


How I would like the data to be,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Account1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]Account2[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]Account3[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[/TR]
</tbody>[/TABLE]


I know this can be done with a simple transpose formula/paste but my dataset is 47,000 rows long, so copying and pasting individually is not an option. I think VBA might be the way to go, but I havent got a clue where to even start.

Help!

Thank you.

 

[Special-K99]

This will transpose the data in column A in groups of 4 (Account number and following 3 rows)

in C1
=INDEX($A$1:$A$12,INT((ROW()-1)*4)+COLUMN()-2,1)

copy across to F1
now copy C1:F1 down as far as row 11,750
or until you get errors by which time the column A data will have expired