Transform single column into multiple columns & rows

[henryg]

I have data in this format
[TABLE="width: 50, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]i[/TD]
[/TR]
</tbody>[/TABLE]
and I want

[TABLE="width: 50, align: left"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 50, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[/TR]
</tbody>[/TABLE]

for about a thousand rows; and preferably not a macro.

Pretty sure this must be a common problem, but couldn't find it here. I thought it was in one of Bill's videos but again couldn't find it.

Thanks

 

[Fluff]

Have a look here
https://www.ablebits.com/office-addins-blog/2014/03/06/transpose-excel-rows-columns/

 

[henryg]

The above either doesn't do it, or I can't get my head around it. I have been able to get a helper column in place so that the data then looks like (so 3 columns)

A d 1
B e 1
C f 1
A g 2
B h 2
C i 2
A j 3
B k 3
C l 3

etc etc

and I still want the output to be in 3 columns - helper column shown but not needed

A B C Helper
d e f 1
g h i 2
j k l 3

It's driving me crazy