How can I find and merge these duplicates from a list of 1000's of accounts with incomplete info?

How can I merge a list of companies with many duplicates but the duplicates aren't exact matches?

Account #   First Name  Last Name   Company                  Address              City   ST
            Lisa        Miles       Bills Industrial Supply  2212 E Main Street   Denver      
#41519456                           Bill's INDUSTRIAL SUPPLY 2212 E Main Street          
#41519456   Lisa        M           Bill's INDUSTRIAL                                    CO

final version:

Account #   First Name  Last Name   Company                  Address              City   ST
#41519456   Lisa        Miles       Bill's Industrial Supply 2212 E Main Street   Denver CO

Jeff Jeffries

Posted 2015-01-19T22:17:13.420

Reputation: 1

1You need to use a unique identifier to pinpoint duplicates, for example by customer number (41519456). Then handle those duplicates manually since there is no programmatic way to know which address or business name is more correct than another. – Moses – 2015-01-19T22:35:12.697

Answers

I would use the Power Query Add-In for this. It has a Group By command so you can collapse multiple rows by Account #.

https://support.office.com/en-au/article/Group-rows-in-a-table-e1b9e916-6fcc-40bf-a6e8-ef928240adf1

Your exact requirements on how to derive the "final version" are a unclear, but Power Query has lots of data manipulation functionality to automate any further tweaks needed. The simplest option would be to apply the Max operation to all the other columns in the Group By window.

Mike Honey

Posted 2015-01-19T22:17:13.420

Reputation: 2 119