SOLVED

Excel - How do find duplicates (without removing) from a large database? Hide unique values?

%3CLINGO-SUB%20id%3D%22lingo-sub-2538325%22%20slang%3D%22en-US%22%3EExcel%20-%20How%20do%20find%20duplicates%20(without%20removing)%20from%20a%20large%20database%3F%20Hide%20unique%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538325%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20huge%20database%20that%20I%20need%20to%20check%20for%20duplicates.%20Understand%20that%20one%20of%20the%20way%20is%20using%20conditional%20formatting%20to%20highlight%20the%20field%20(in%20different%20colours)%2C%20however%20this%20doesn't%20work%20well%20for%20me%20as%20I%20have%2030%2C000%20lines%20to%20eyeball%20the%20duplicates.%20Is%20there%20another%20way%20to%20find%20the%20duplicates%3F%26nbsp%3B%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2538325%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538363%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20How%20do%20find%20duplicates%20(without%20removing)%20from%20a%20large%20database%3F%20Hide%20unique%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101352%22%20target%3D%22_blank%22%3E%40WMGoh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20helper%20column%20to%20filter%20duplicates%2Funique%20values.%20If%20you%20are%20on%20Excel%20365%20you%20may%20use%20%3DCOUNTIFS(range%2C%20range)%2C%20if%20not%20%3DCOUNTIFS(range%2C%20cell)%20and%20Ctrl%2BD%20it%20down.%3C%2FP%3E%0A%3CP%3EValues%20%26gt%3B1%20in%20this%20column%20indicate%20duplicates%2C%20use%20filter%20on%20all%20but%201.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2540873%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20How%20do%20find%20duplicates%20(without%20removing)%20from%20a%20large%20database%3F%20Hide%20unique%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2540873%22%20slang%3D%22en-US%22%3EBrilliant!%20Thanks.%3C%2FLINGO-BODY%3E
New Contributor

I have a huge database that I need to check for duplicates. Understand that one of the way is using conditional formatting to highlight the field (in different colours), however this doesn't work well for me as I have 30,000 lines to eyeball the duplicates. Is there another way to find the duplicates?  Thanks.

 

3 Replies
best response confirmed by WMGoh (New Contributor)
Solution

@WMGoh 

You may use helper column to filter duplicates/unique values. If you are on Excel 365 you may use =COUNTIFS(range, range), if not =COUNTIFS(range, cell) and Ctrl+D it down.

Values >1 in this column indicate duplicates, use filter on all but 1.