Combine data between rows with duplicate values in multiple columns, using a primary key

Question

Hi there,

I've search for a solution but no luck until now.

I have a client table with multiple duplicates (24000 rows..) from different sources and need to combine them.

For example:

SocialNum	Name	Citizen	Email
654654	john doe	13513513	******@company.com
654654	john mid doe		******@company.com

What I'm trying to achieve is a way to select the row that has more data available (more cells filled and not blank) and use it for a new table, using the "SocialNum" ID as the validation for duplicates.

So I would get:

SocialNum	Name	Citizen	Email
654654	john doe	13513513	******@company.com

If there are two duplicate rows with the same amount of data, copy the first one.

Also if in 10 possible fields (columns) one row has 9 and the other row also has 9 but are blank on different fields, select the first one and combine the blank information with data from the other row.

Example:

SocialNum	Name	Citizen	Email	field5	field6
654654	john doe	13513513	******@company.com	has
654654	João Meijinhos		******@company.com	has	also

Would get:

SocialNum	Name	Citizen	Email	field5	field6
654654	john doe	13513513	******@company.com	has	also

Is this possible?

Thank you!

Answer 1

Hi Joao

A few days has elapsed. No news. What does this mean?

Answer 2

Hi Joao

With Get & Transform aka Power Query

If not exactly what you want that shouldn't be far. Sample avail. here:

• Copy/paste your data in Table1
• Switch to Sheet2
• Right-click somewhere in the green table > Refresh (might take a bit of time with 24k rows...)

Only mandatory column name is the first (SocialNum). All others can be renamed at your convenience