39,000 Row excel sheet needs duplicates removed but more sophisticated than regular duplicates

Occasional Visitor

I have a 39,000 row sheet containing Cell A - Name, Cell B - Phone, Cell C - Email Address, and Cell D -  Address.  

 

I need to exclude duplicate records in a report. My preference would be to prioritize records by:

  • those with an email address
  • those with more info populated than less (for example, if one has an address and the other does not
  • for records that have the same amount of info populated, but different data, I have no preference which record would be kept

So below is 5 rows from my excel (this is generic info used for the example) and I need to consolidate it to one row per Name and ideally have data in all of the above named cells....(THERE IS NO PREFERANCE OF WHICH DATA ENDS UP IN THE REMAINING CELLS WHERE THE DATA IS DIFFERENT...WE JUST NEED THE CELL NOT EMPTY AND THE EXTRA ROWS GONE)

       
Mandy Thompson555-444-1155email address458 S. Washington StNewark NJ77855
Mandy Thompson email address ADDRESS   
Mandy Thompson  888 W. Joseph RdAlbertaWA45877
Mandy Thompson485-999-6584email address ADDRESS   
Mandy Thompson555-444-1155email address 458 S. WashingtonNewark NJ77855
1 Reply