How can I Merge Duplicate Rows in Excel Combining Unique Values from multiple columns and rows in One Cell

Question

Hello, I would like to merge duplicate rows using column A unique identifiers, and merge values from multiple columns in one row.

Here's an example of two rows I'd like to combine into one.

	A	B	C	D
1	Name	Book read (1)	Book read (2)
2	Bob	Help with Excel	The Crow
3	Bob	The Crow	Great Expectations

How can the TEXTJOIN function be edited to include data from both Book read (1) column as well as Book read (2) column to give the following format:

Name	Books read
Bob	Help with Excel;The Crow;Great Expectations

To ensure no duplicated values but to ensure all values are in one row from both columns and both Bob rows. Does anyone have a workaround for this? I can't find the answer anywhere. I have used the TEXTJOIN function but it seems to only be able to merge and join values from the same column. Can it be used to join data within multiple columns as there may be duplicate values across columns? In my real life spreadsheet, it's actually a selection of 15 columns that I need to merge / deduplicate values from.

Answer 1

For example:

The formula in E2 is

=UNIQUE(A2:A5)

And the formula in F2 is

=TEXTJOIN("; ", TRUE, UNIQUE(TOCOL(FILTER($B$2:$C$5, $A$2:$A$5=E2))))

Answer 2

Thank you so much for this!!

Answer 3

Hi,

This M code in Power Query works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"), 

#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), 

#"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Count", each Text.Combine(List.Distinct(\_[Value]),", ")}}) 

in

#"Grouped Rows"

Hope this helps.

Answer 4

Hi,

Below I am giving you a formula to achieve your goal. I know the formula is a bit long, but I think it works. The formula is dynamic, so you can apply it only once in a specific cell, for example in cell E1.

=LET(tr, TRIMRANGE(A2:C20), unq, UNIQUE(HSTACK(TRIM(TEXTSPLIT(ARRAYTOTEXT(REPT(UNIQUE(SORT(TAKE(tr,, 1))) & ",", BYROW(DROP(GROUPBY(TAKE(tr,, 1), DROP(tr,, 1), COUNTA,, 0),, 1), SUM))),, ",", TRUE)), TEXTSPLIT(ARRAYTOTEXT(BYROW(DROP(SORT(tr),, 1), ARRAYTOTEXT)),, ", "))), IFERROR(VSTACK({"Name","Books read"}, GROUPBY(TAKE(unq,, 1), TAKE(unq,, -1), LAMBDA(a, TEXTJOIN("; ",, a)),, 0)), ""))

The formula requires the use of Excel 365.

Hope this helps.

Answer 5

> ... the formula is a bit long...

Just for fun feedback, your codes are always 5 times more complex then necessary, especially with your string algorithms.

But... at least you are trying, and not using multiple formulas, and dragging down. Yeah. :>)

> Mathematica and Inverse Equations

Excellent!! Very nice. :>)