Data Transpose question

[Fridaybird]

I have my data in the following format:

[TABLE="width: 262"]
<colgroup><col></colgroup><tbody>[TR]
[TD] AMERICAS\A.GIRARD[/TD]
[/TR]
[TR]
[TD] Alejandro[/TD]
[/TR]
[TR]
[TD] Girard[/TD]
[/TR]
[TR]
[TD] No[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] AMERICAS\A.M.LAWRENCE[/TD]
[/TR]
[TR]
[TD] Alex[/TD]
[/TR]
[TR]
[TD] Lawrence[/TD]
[/TR]
[TR]
[TD] Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] AMERICAS\A.MATVIENK[/TD]
[/TR]
[TR]
[TD] Alexander[/TD]
[/TR]
[TR]
[TD] Matvienk[/TD]
[/TR]
[TR]
[TD] No[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] AMERICAS\AARON.FUHRR[/TD]
[/TR]
[TR]
[TD] Aaron[/TD]
[/TR]
[TR]
[TD] Fuhrr[/TD]
[/TR]
[TR]
[TD] No

Please note that there is a gap of one row between each set.
I need this data to be organized in the following manner, either on same sheet or the new sheet does not matter:

[TABLE="width: 374"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD] AMERICAS\A.GIRARD[/TD]
[TD] Alejandro[/TD]
[TD] Girard[/TD]
[TD] No[/TD]
[/TR]
[TR]
[TD] AMERICAS\A.M.LAWRENCE[/TD]
[TD] Alex[/TD]
[TD] Lawrence[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD] AMERICAS\A.MATVIENK[/TD]
[TD] Alexander[/TD]
[TD] Matvienko[/TD]
[TD] No[/TD]
[/TR]
[TR]
[TD] AMERICAS\AARON.FUHRR[/TD]
[TD] Aaron[/TD]
[TD] Fuhrr[/TD]
[TD] No[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

Please help.

Regards

Imran

 

[NeonRedSharpie]

Sub transposeData()

    For x = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 1
            
        Cells(x, 2) = Cells(x + 1, 1)
        Cells(x, 3) = Cells(x + 2, 1)
        Cells(x, 4) = Cells(x + 3, 1)
        
        Range(Cells(x + 1, 1), Cells(x + 4, 1)).Delete
            
    Next x


End Sub

Be sure to save before testing as this will delete data....of course it should move the data first....

 

[Fridaybird]

little non techy person here, can you forward Macro or formula for this?
Or how to run your code?

Kind regards

Imran

 

[NeonRedSharpie]

If this is only impacting one sheet, you can right click on that sheet and hit "View Code", then paste this in. Then you can run it by pressing F5.

 

[Fridaybird]

Thanks a Million - it worked like breeze.

God bless you.

Imran

 

[Fridaybird]

Dear NeonRedSharpie,

Your code worked like a Jet,, but it missed last line where Yes/No is decided. Can you please revise and make that minor change to include full information please?
After the code data is in following format:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"] AMERICAS\A.GIRARD[/TD]
[TD="width: 64"] Alejandro[/TD]
[TD="width: 64"] Girard[/TD]
[/TR]
</tbody>[/TABLE]

Required is:
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]AMERICAS\A.GIRARD[/TD]
[TD="width: 64"] Alejandro[/TD]
[TD="width: 64"] Girard[/TD]
[/TR]
</tbody>[/TABLE]
NO
Thanks again for your instant help.

Imran