Forum Discussion
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 duplicate records based on conditional formatting of the cell. It seems as if the duplicate tool has overlaid the cell formatting and not applied the change to the cell therefore the find tool cannot be used to locate the duplicate records. I have verified this by looking at this cell format and the text that supplied by the duplicate tool does not show up and is therefore just overlaid on the cell and not applied to it. My question is twofold one is there a way to make the overlay permanent? And secondly is there a better way to Search for duplicates in my large spreadsheet.
I appreciate any assistance.
16 Replies
- You just want to highlight the duplicated rows or remove them.
To remove the duplicated rows, from Data tab --> Data tools --> click Removed duplicates.
To just find the duplicated rows, you can used vlookup functionJihad Al-Jarady I can't remove the duplicate rows, they provide necessary information that will be used to review another application. My goal is to quickly find them.
After applying conditional formatting to highlight duplicates, you can turn on Filter, then select Filter by Color to select only the duplicates.
HansVogelaar I tried that in the formatting from the duplicate tool does not apply to the cells it basically overlays the color onto the cell. If I manually apply the color to the cell then the find tool works only on the one that I manually apply the color. The find tool skips over any color that is applied by the find duplicate tool. I hope that makes sense.
I agree with using the conditional formatting to highlight all duplicates. From there you can create a new Helper column and input a 1 for all rows that show up in the filter to make it easier to identify all rows that are duplicates.
1. Conditional Format
2. Filter by Color
3. Helper Column
