How to remove duplicate data based on unique values and collate all comments in one cell

[ThrottleWorks]

Hi,

Please check attached file for more details.

I am trying to collate data, once the collating part is done, I need to remove duplicate values based on the unique identifier in column A.

After that, I need to collate all the comments for the particular unique identifier in column M as coma separator.

Can anyone please help me in this.

 

[p45cal]

You can try something like this:

Sub Macro2()
'you might need to sort by column A first..
Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 13), Header:=xlYes
Range("A1").CurrentRegion.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(16), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Set rngz = Range("A1").CurrentRegion
For Each are In Intersect(rngz, rngz.Offset(1)).Columns("M").SpecialCells(xlCellTypeConstants, 23).Areas
  If are.Cells.Count > 1 Then are.Value = Join(Application.Transpose(are.Value), ", ")
Next are
rngz.RemoveSubtotal
rngz.RemoveDuplicates Columns:=Array(1, 13), Header:=xlYes
End Sub

It works on the active sheet. See attached and buttons on the input sheets.

 

[ThrottleWorks]

Hi @p45cal thanks a lot ! Yes, I will sort the data. No issues.

It is working perfect. Have a nice day ahead.