Combine duplicate rows with unique ID and maintain columns

Question

Hello world!

I have a worksheet where there are multiple rows with the same unique identifier, but each row has several columns of data. I want to combine them down to one row per unique identifier while keeping the columns. If all rows with the same ID have blanks, leave the column blank. If any column has data, consolidate it down to the one row. Data will not overlap/overwrite in the worksheet due to the nature of the data. I have absolutely no idea how to get this done without going one at a time and I have over 1,000 rows. Please help!!!!

The table might look like:

Name	Value A	Value B	Value C
Bob	Book	Chair
Sally	Zebra
Bob			Vanilla
Sally		Tablet

and what I want to get would be:

Name	Value A	Value B	Value C
Bob	Book	Chair	Vanilla
Sally	Zebra	Tablet

I really could use a life saver here... I'm desperate and have to submit the data table ASAP.

Answer 1

Hello Ross A,

I'm Shalom and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

I understand your situation. Here are a few methods you can use to consolidate rows with the same identifier in Excel:

Method 1: Using the Excel Consolidate tool

Go to Data > Consolidate. In the Consolidate dialog box, accept the default SUM function, under Reference select the data range, click the Add button, check the Top row and Left column options, and then click OK.

Method 2: Using IF function with helper columns

Select the dataset and go to Data > Sort A to Z. Add Helper column 1 and enter the formula =IF(A2=A1,B1&”,”&B2,B2) in cell C2. Press Enter key and drag the Fill Handle button to the rest of the empty cells in the helper Column C.

Add Helper Column 2 and enter the formula =IF(C2<>B2,”Combined”,””) in cell D2. Press Enter key and drag down the Fill Handle to the empty cells.

Please let me know if you need more help.

Best Regards, Shalom

Answer 2

Excel 365 Pro Plus with Power Query.

No formulas, no VBA macro, no helper column.

With 36 rows.

Two configurations with different restrictions.

https://www.mediafire.com/file_premium/l8qnh5xvvt9aaln/11_03_23.xlsx/file

https://www.mediafire.com/file_premium/2exzqoqik7fvtok/11_03_23a.xlsx/file

https://www.mediafire.com/file_premium/7t77vdb9dzhdfdc/11_03_23.pdf/file

https://www.mediafire.com/file_premium/s1nrmmf66x3cifo/11_03_23a.pdf/file

Answer 3

Hi,

This M code works

let

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

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

#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value") 

in

#"Pivoted Column"

Hope this helps.