Find matches, then transpose

[easybreezy]

Hi all,

I'm looking at a data set with tens of thousands of rows similar to the dummy data file I have attached. I am wondering if there is a macro to to search through a column and if Ai+1=Ai, Ai+2=Ai,etc... copy/paste the values of column B after Ai (Bi+1, Bi+2, etc.) into columns C, then D, etc. of row i.

Not sure if this is clear, but the sample file shows a current state and and target state tab, with some nuances in the other columns.

The goal is to search through a list of names (or in theory the corresponding ID #) and transpose the list of descriptions for the name into a single row.

Thank You!

 

[Marc L]

Hi !

Welcome on Chandoo !

Pretty unclear, fortunately a workbook was attached !

As any beginner can achieve using Macro recorder and VBA inner help :​

Sub Demo4Noob()
    With [A1].CurrentRegion.Columns(1)
              F& = 2
        While F < .Rows.Count
              L& = .Find(.Cells(F).Value, , xlValues, xlWhole, , xlPrevious).Row
           If L > F Then
              With Range(Cells(F + 1, 21), Cells(L, 21))
                .Copy
                 Cells(F, 1).End(xlToRight)(1, 2).PasteSpecial Transpose:=True
                .ClearContents
              End With
           End If
              F = L + 1
        Wend
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​