Avatar of Sam OZ
Sam OZ
Flag for Australia asked on

Macro to get list grouped from rows

I have an MS-excel spreadsheet  

Tag         Document

-----         ----------

Tag1         Doc1

Tag2        Doc1

 Tag3        Doc1

 Tag4        Doc2 

  Tag1       Doc1


I am looking a macro which can get the result in a spreadsheet following way ( listing all Tags in a document listed in a row.   )

Listing to be comma separated . Tag1,Tag4 can be Tag4,Tag1  also ( Sorting alphabetically is desirable not essential) 

Document   TagList

---------        -----------

Doc1             Tag1,Tag2,Tag3

Doc2              Tag1,Tag4

* MS Excel MacroMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Peter Chan

8/22/2022 - Mon
Martin Liss

To get your desired results the first Tag1 would need a Document of Doc2. This workbook has that change and it includes the following macro which can be executed by pressing Ctrl+Shift+T

Sub Tags()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngNextRow As Long
Dim lngEntry As Long
Dim colDocs As New Collection

lngLastRow = Range("A1048576").End(xlUp).Row
lngNextRow = 1

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

For lngRow = 2 To lngLastRow
    On Error Resume Next
    colDocs.Add Cells(lngRow, "B"), CStr(Cells(lngRow, "B"))
    On Error GoTo 0
Next

ActiveSheet.UsedRange.Cells.Offset(1, 3).ClearContents
For lngEntry = 1 To colDocs.Count
    lngNextRow = lngNextRow + 1

    For lngRow = 2 To lngLastRow
        Cells(lngNextRow, "D") = colDocs(lngEntry)
        If Cells(lngRow, "B") = colDocs(lngEntry) Then
            If IsEmpty(Cells(lngNextRow, "E")) Then
                Cells(lngNextRow, "E") = Cells(lngRow, "A")
            Else
                Cells(lngNextRow, "E") = Cells(lngNextRow, "E") & "," & Cells(lngRow, "A")
            End If
        End If
    Next
Next

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

Open in new window

29224633.xlsm
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy