How to combine duplicate rows into one (keeping unique data only)

Question

Dear experts,

I have a list with thousands entries, first column has duplicate values, while values in the second column are different.

What I am trying to achieve is shown in the screenshot below:

1. Column 1: To merge unique values only and omitting duplicates.
2. Column 2: Combine data from those duplicate rows into one cell, separated by comma.

Is there a function in Excel to achieve this, can Power Query do this?

I do appreciate your kind support.

Answer 1

Don't format the data on the right as a table.

Enter the following formula in D5:

=UNIQUE(Table1[Key])

where Table1 is the name of the table on the left. The formula will automatically spill to the cell(s) below.

In E5, enter the formula

=TEXTJOIN(", ",TRUE,IF(Table1[Key]=D5,Table1[Data],""))

and fill or copy down.

Answer 2

Hi,

You may refer to 2 solutions in this workbook - Query Editor and formulas.

Hope this helps.

Answer 3

Don't worry - it might well be useful to users who don't have the latest version of Excel.

Answer 4

Hi,

You may refer to 2 solutions in this workbook - Query Editor and formulas . . .

Thank You Ashish Mathur.

I am learning Power Query. When I solved this question using formulas, I was also curious to know the solution using PQ.

Thank You!

Answer 5

You are welcome.