I am trying to create a macro in excel that merges rows with duplicate data in columns A, B and D into a single row that showing that data while also pulling in the data from column e from each of those and delineates by a semicolon.

Group Product description, Sign Type, Quantity -> join Distribution in semicolon delimited string

As an example I have the below 3 rows right now that read as follows

I want the macro to run and consolidate them so instead of 3 lines I have 1 and it reads

I would want it to run through the whole sheet and consolidate like that. I can't seem to find any VBA code that would work. I started with this. But it just errors. Any help SUPER appreciated!

Sub CreateDistributionReport()
' TODO: Group Product description, Sign Type, Quantity -> join Distribution in semicolon deliminated string

' find product description, sign type, quantity
' while unchanged
With Sheets("Output")
Dim groupedProductDescription As Long
groupedProductDescription = .Rows(1).Find(what:="A Shoc 16 OZ B1G1 for a $1").Column

Rows(groupedProductDescription).Select
End With
End Sub

CodePudding user response：

Sub CreateDistributionReport()
' TODO: Group Product description, Sign Type, Quantity -> join Distribution in semicolon deliminated string

' find product description, sign type, quantity
' while unchanged

'OBS!: Assuming columns are sorted

    Dim rStart As Range: Set rStart = Range("A2")
    Dim data, cell As Range
        
    Set data = Range(rStart, rStart.End(xlDown))
    
    For i = data.Rows.Count   1 To 1 Step -1
        
        If Cells(i, 1).Value = a And Cells(i, 2).Value = b And Cells(i, 4).Value = d Then
            Cells(i, 5).Value = Cells(i, 5).Text   ";"   e
            Cells(i, 1).EntireRow.Select
            Cells(i, 1).Offset(1).EntireRow.Delete
        End If
            a = Cells(i, 1).Value
            b = Cells(i, 2).Value
            d = Cells(i, 4).Value
            e = Cells(i, 5).Value
    Next
    
End Sub

Cheers!

CodePudding user response：

If you want a non-macro solution, this seems pretty easy to solve with excel formulas. I am using the smart table feature in excel to make the table easier to work with.

Column Formulas:

• E: =CONCAT([@[Column A]],[@[Column B]],[@[Column C]]) (easier to find matches against a single column)
• F: =COUNTIF([Helper], [@Helper]) (not really needed just curious how many matches per row, and you could sort highest to lowest to see)
• G: =TEXTJOIN("; ", TRUE, IF([Helper]=[@Helper],[Column D]))

Using textjoin() you can concatenate an infinite number of matches into a single string separated by your choice of delimeter. I added a space after the semicolon but you could remove that if you don't like it.

• Then copy Column G and paste as values into a new column, or overwrite column D.
• Then you can highlight the data and click remove duplicates.

• The helper columns are find to keep unless you just don't want them.