Macro to transpose based on what is in the cell

[googlesheet]

Hello!
I'd like to transpose in bulk from this format :

41950

Eve

eva-me@hotmail.de

Eva-

Ku

41949

greg1

gregoire@gmail.com

Gregoire

Mor

41948

At

john@live.com

Jon

Att

<colgroup><col style="width: 100px"></colgroup><tbody>
</tbody>

to this :

41943	Gno	daniel@gmail.com	Ka Le	Lai
41942	Jorge	jorg;e@hotmail.com	Jorge	Gelt
41941	mre	mik.@outlook.com	Michael	Remi
41940	pla	a@spb.org	Julia	Plavins

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

If you could give me a function for this in excel or google sheet javascript format this would be great!
Thank you very much

 

[Gary McMaster]

Hi Google,

Welcome to the board!

Try the following sample in a standard module:

Public Sub Test()

Dim oCell As Range
Dim oTarget As Range
Dim oTranspose As Range

Set oTarget = ActiveSheet.Range("A1:A" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)
Set oTranspose = ActiveSheet.Range("B1")

For Each oCell In oTarget

    oTranspose.Value = oCell.Value
    
    If Val(oCell.Offset(1, 0).Value) <> 0 Then
        Set oTranspose = oTranspose.Offset(1, ((oTranspose.Column - 2) * -1))
    Else
        Set oTranspose = oTranspose.Offset(0, 1)
    End If
    
Next oCell

End Sub

 

[googlesheet]

Hi Google,

Welcome to the board!

Try the following sample in a standard module:

Public Sub Test()

Dim oCell As Range
Dim oTarget As Range
Dim oTranspose As Range

Set oTarget = ActiveSheet.Range("A1:A" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)
Set oTranspose = ActiveSheet.Range("B1")

For Each oCell In oTarget

    oTranspose.Value = oCell.Value
    
    If Val(oCell.Offset(1, 0).Value) <> 0 Then
        Set oTranspose = oTranspose.Offset(1, ((oTranspose.Column - 2) * -1))
    Else
        Set oTranspose = oTranspose.Offset(0, 1)
    End If
    
Next oCell

End Sub

Hmmm how can I say .....

YOU ROCK !!! YOU ARE THE MAN!!!

THANK YOU SO MUCH I could never do it without you!!!