Macro that transposes information

Mamikosora

New member
Joined
Mar 9, 2021
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2016
Good afternoon!

I need your help to make a macro that does the following:

They give me the data in this way (all this information is in column A):

Categoría 1
a
b
c
d
b1
c1
d1
Categoría 2
z
w
x
y
xy
w1
x1
y1
w2
x2
y2
xy2
w3
x3
y3
w4
x4
y4
xy4

And the macro should convert it like in this table.

Categoría 1abcd
b1c1d1
Categoría 2wxyxy
w1x1y1
w2x2y2xy2
w3x3y3
w4x4y4xy4

At the moment I have this as a code but it does not do me well

Code:
Option Explicit
Option Base 1


Sub obtener()


Dim r As Range, fr%, cr%
    Set r = Range("A1").CurrentRegion


Dim z As Object, zs$, M(1 To 5)
    Set z = CreateObject("scripting.dictionary")
Dim K As New Collection, ks$, kn%




On Error Resume Next 'para la K
    For fr = 1 To r.Rows.Count
        
        zs = r(fr, 1).Row


        If r(fr, 1) Like "PO=*" Then


            ks = ""
            For cr = 2 To 4
                ks = ks & r(fr + cr, 1)
            Next
            K.Add ks, ks




            If K.Count > kn Then
                kn = K.Count
            Else
                fr = fr + 5
                GoTo sigue
            End If


        
            M(1) = r(fr, 1)
            fr = fr + 1
            M(2) = r(fr, 1)
            fr = fr + 1
            M(3) = r(fr, 1)
            fr = fr + 1
            M(4) = r(fr, 1)
            fr = fr + 1
            M(5) = r(fr, 1)
            z.Add zs, M()
        Else
            ks = ""
            For cr = 0 To 2
                ks = ks & r(fr + cr, 1)
            Next
            K.Add ks, ks


            If K.Count > kn Then
                kn = K.Count
            Else
                fr = fr + 2
                GoTo sigue
            End If


'            zs = r(fr, 1).Row
            M(1) = Empty
            M(2) = Empty
            M(3) = r(fr, 1)
            fr = fr + 1
            M(4) = r(fr, 1)
            fr = fr + 1
            M(5) = r(fr, 1)
            z.Add zs, M()
        End If
sigue:
    Next


    Columns("C:J").ClearContents
    Range("C2").Resize(z.Count, 5) = Application.Index(z.items, 0, 0)
End Sub

I would greatly appreciate your help!

Links:
https://www.mrexcel.com/board/threa...-information-as-follows.1164330/#post-5653763
https://www.excelforum.com/excel-pr...s-the-information-as-follows.html#post5484569
https://techcommunity.microsoft.com...the-information-as-follows/m-p/2196371#M92155
 
Back
Top