Transpose data in columns from sheet 1 to rows as in sheet 2.

[bwaaack]

Hi! I need help to create a formula that will transpose data in columns from sheet 1 to rows as in sheet 2. Thank you in advance.

Sheet1

Item#	Image	URL
10011-BKBK	10011-BKBK_01.jpg	assets/images/MEM_MAIN/10011-BKBK_01.jpg
10011-BKBK	10011-BKBK_02_TABLETOP.jpg	assets/images/MEM_ALT/10011-BKBK_02_TABLETOP.jpg
10011-GRGR	10011-GRGR_01.jpg	assets/images/MEM_MAIN/10011-GRGR_01.jpg
10011-GRGR	10011-GRGR_02_FACE.jpg	assets/images/MEM_ALT/10011-GRGR_02_FACE.jpg
10011-GRGR	10011-GRGR_03_GIRL.jpg	assets/images/MEM_ALT/10011-GRGR_03_GIRL.jpg
10011-RDBL	10011-RDBL_01.jpg	assets/images/MEM_MAIN/10011-RDBL_01.jpg

<tbody>
</tbody>

Sheet 2

Item#	image	image	image	image	image
10011-BKBK	assets/images/MEM_MAIN/10011-BKBK_01.jpg	assets/images/MEM_ALT/10011-BKBK_02_TABLETOP.jpg
10011-GRGR	assets/images/MEM_MAIN/10011-GRGR_01.jpg	assets/images/MEM_ALT/10011-GRGR_02_FACE.jpg	assets/images/MEM_ALT/10011-GRGR_03_GIRL.jpg
10011-RDBL	assets/images/MEM_MAIN/10011-RDBL_01.jpg

<tbody>
</tbody>

 

[DanteAmor]

Try this

Sub Transpose_data()
    Dim datos As Range, i As Long, n As Long
    Set datos = Sheets("Sheet1").Range("A1").CurrentRegion
    For i = 2 To datos.Rows.Count
        n = WorksheetFunction.CountIf(datos.Columns(1), datos(i, 1))
        lr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).Row
        Sheets("Sheet2").Range("A" & lr).Value = datos(i, 1)
        Sheets("Sheet2").Range("B" & lr).Resize(, n).Value = WorksheetFunction.Transpose(datos(i, 1).Resize(n, 1).Offset(, 2).Value)
        i = i + n - 1
    Next
End Sub

 

[bwaaack]

Dante, thank you very much. The formula worked better than expected. Great work!

 

[DanteAmor]

Dante, thank you very much. The formula worked better than expected. Great work!

I'm glad to help you. I appreciate your kind comments.