Combining duplicate records with unique data in Excel

Question

I have an Excel database and I try to avoid manually combining duplicate data. I have a list of lists that are essentially the same except for the tag column. What I would like to do is combine these 5 entries into 1 listing, making the categories separated by commas in one cell.

Turn it

in that

Is there any way to achieve this? My document contains several thousand entries, so I obviously try to avoid the manual editing route. I am new to Excel, so any manual work or tutorials you could point me to would be appreciated.

Answer 1

This can also be done using formulas. For my work example, the data should be sorted by the first column, and there should be a title bar.

You will need two more columns (C and D). First, add a formula that essentially says to concatenate the data in column B if the data in column A matches the row above, otherwise reset the concatenation. The next column will contain a formula for determining the final concatenations so you can sort later.

Here's how I would do it with listings and categories in columns A and B (again, the data would need to be sorted by column A, and there should be a header row):

Here are the results. Now I would copy the entire range and paste the values ​​into another sheet. Rows with zero for column D is what I would like to use. Sorting by column D will move them up.

Answer 2

This will (should) generate a new sheet from your source file with merged duplicates.

To use the following code, you need to add it to the new module in the VBA editor

The shortcut to open the VBA editor is Alt+F11 (for Windows) and Alt+Fn+F11 (for Mac)

When the editor is open, add a new module by selecting it from the "insert" menu in the main menu bar. It should automatically open the module, ready to accept the code, if you do not need to select it (it will be called "ModuleN", where N is the next available number) from the project explorer.

I'm not sure if "Scripting.Dictionary" is available in osx, but it can't hurt to try.

 Option Explicit Sub Main() Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1") Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet2") Dim Records As Object: Set Records = CreateObject("Scripting.Dictionary") Dim Data As Variant Dim Index As Long Dim Row As Integer: Row = 1 Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2 For Index = LBound(Data, 1) To UBound(Data, 1) If Records.Exists(Data(Index, 1)) Then Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & ", " & Data(Index, 2) Else Records.Add Data(Index, 1), Row Destination.Cells(Row, 1).Value2 = Data(Index, 1) Destination.Cells(Row, 2).Value2 = Data(Index, 2) Row = Row + 1 End If Next Index Set Records = Nothing End Sub