Unique row values

[tamlynn]

Hi there! I need some help with a large excel doc in office 365. It has 50,000 rows and 10 columns (the first two columns are first name and last name). The issue: names are appearing in duplicate, triplicate and more.  What I need is to either move all unique row values to another worksheet OR delete all row entries that are not unique ones. Either way, i need the data in all 10 columns to remain with the row data. I've tried some "unique" formulas but none seem to result in the data in all 10 columns remaining. I can attach a sample excel with mock data if that helps. Thanks very much!

[#2]

@tamlynn 

I can attach a sample excel with mock data if that helps.

It would help immeasurably. Attach here, or if that's not possible, put it on OneDrive or GoogleDrive with a link pasted here.

[#3]

@mathetes thank you for your reply. Here is a link to a sample excel with mock data. Thanks!

https://docs.google.com/spreadsheets/d/1GPmoJ7r1lxC1i9_iO9GUJhFZDzdmnuAJ/edit?usp=drivesdk&ouid=1113... 

[#4]

Hi @tamlynn 

Your link doesn't work and IMHO mock data are useless when it comes to identifying duplicates, especially with text values where the unexpected can be expected :))

With 50k rows attached is a Power Query option that does - on the first 2 columns:
- Clean as much as possible invisible/unprintable chars
- Remove extra spaces everywhere
- Capitalize 2nd column (if you want Last Name this can be changed easily)

NB

• If you have exceptions like "DA - VINCI", "DA-VINCI" and "DA    -   VINCI" you'll get 2 distints
• If your data result of a copy/paste from the Web or another application it remains possible that some invisible/unprintable chars won't be removed and consequently impact the result

How to use:
- Download the attached file
- Copy/Paste as values your data in TableSource (sheet Data Source)
(you can rename the table column names the query will auto. adjust)
- Go to Excel Data tab > Refresh All
You get 2 results: Distincts & Duplicates

(In TableSource examples all colored cells have extra spaces here & there)

[#5]

@tamlynn 

As @L z. has already noted, the link doesn't work as it is. You have to set it up with a link that grants access to any who have it.