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

     

     

rkozak's avatar
rkozak
Copper Contributor
Apr 14, 2021

adversi GMKubala 

Hi there, can you explain this a little better? I have 109,749 rows of a spreadsheet that I need to check/view duplicates in order to correct them.

adversi's avatar
adversi
Iron Contributor
Apr 15, 2021

rkozak Can you tell me what step you're lost in? The breakdown before with screenshots should cover all the steps

  • rkozak's avatar
    rkozak
    Copper Contributor
    Apr 15, 2021

    I believe I understand what you're doing; you click the dropdown box where you can select variables to be included in your filter/sort for that given column. It is there that I can see filter by colour. I have tried this on another smaller list and it works easily.

    Unfortunately, due to the sheer size of the file my computer always freezes and is unable to process even opening the drop down box because we have 109,749 unique values in that column. These are where the duplicate values are the utmost of importance because they actually correspond to serial numbers we intend to use.

    • 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","")

         

         

Resources