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

Rand,

Of course you are a contributor to this forum just for fun feedback :>)

In my opinion OP should not worry about how big the formula is, but it is enough that that formula produces the results he expects.

However, you have the opportunity to provide your solution with a formula that will not be complex at all. as mine.

Note: I saw Inverse Equations in a math book.