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?
OliverScheurich
Feb 01, 2024Gold Contributor
=IF(COUNTIF($A$2:$A$23,A2)>1,A2,"")
If the result has to be the same size of 100000+ rows you can apply this formula to remove the unique values or
=IF(COUNTIF($A$2:$A$23,A2)=1,A2,"")
to remove the duplicates.
=BYROW(A2:A23,LAMBDA(x,IF(COUNTIF(A2:A23,x)>1,x,"")))
This formula would spill the result but is only available in Office 365 and Excel for the web.
Spilled result with LAMBDA in Excel for the web or Office 365:
Result in legacy Excel e.g. Excel 2013: