Forum Discussion
Eli_Cohen830
Feb 01, 2024Copper Contributor
How to clear unique values and stay with duplicates?
Hi I have a sheet with 100,000+ rows and some of them are duplicates that I need to identify and delete from the system how would i do it?
m_tarler
Feb 01, 2024Bronze Contributor
If you want to see all the lines that are duplicate you can use:
=FILTER(range, ISERROR(XMATCH(range, UNIQUE(range,,1))),"")
note that this will show ALL the duplicates so if you delete all the lines it found you wont have it in the list at all. So if "John" is listed 2x it will show "John" 2x. This could be useful if this is part of a bigger table and you need to decide which line(s) to delete (replace 1st 'range' with the table range to see more columns).
If you are just trying to get a 'clean' list without duplicates it would be much easier to just get the UNIQUE() list. Then if you want to delete the list with duplicates, copy the resulting UNIQUE list and Paste As Values and then you no longer need the original.