Forum Discussion

GMKubala's avatar
GMKubala
Copper Contributor
Dec 09, 2020
Solved

Finding duplicates in a large excel sheet

I have a very large spreadsheet of approximately 316,000 rows. I have used the conditional formatting tool to help me find duplicates. My next step is to use the find tool and search for the duplicat...
excel
  • adversi's avatar
    adversi
    Dec 09, 2020

    GMKubala

    Manual input once you filter by color, you copy and paste to the last row (instead of dragging). 

    Then you can Clear the Conditional Formatting to revert the file size back to normal

     

     

adversi's avatar
adversi
Iron Contributor
Apr 15, 2021

rkozak an alternative to avoid filtering and coloring can be to use the COUNTIF function

=COUNTIF(A:A,A10)-1

 

Any figure in the helper column that is greater than 0 has a duplicate.

 

hookylife's avatar
hookylife
Copper Contributor
Feb 15, 2022

One other way to get results without watching Excel spin for 20 minutes: You can sort the sheet by that column before doing anything else, and use a pretty simple If formula to check the values above and below to flag duplicates.

=IF(OR(D2=D1,D2=D3),"Y","")