Windows Forum

Ask something
Discussion Locked

Excel - Find Duplicates in a List?

0 helpful
8 replies

by dfowler

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Am wondering if there is the ability with Excel to find duplicates in a list of data. For example, if I have a list of customer #'s and their related sales activity. Short of sorting the list and looking through it manually, is there a way to haveExcel tell me which customer numbers are duplicates (or triplicates, etc.) - that I can see at a glance which customers have more than one activity? It is a large list (thousands of lines). Would appreciate any insight anyone has in this regard. Thanks!

8 replies

by dklippert

You could also get fancy by expanding on:
=IF(COUNTIF(range1,A2)>2,"Multiples",IF(COUNTIF(range1,A2)>1,"Duplicate",""))

by e.rick

You could also use Conditional Formatting to make the duplicate entries stand out. Assuming your range of customer numbers is named "CustNums" then:

1. Select the range of cusotmer numbers (or select the whole column).
2. Open the Conditional Formatting dialogue (Format menu, Conditional Formatting... option).
3. Select "Formula Is" in the drop down.
4. Enter the following formula in the entry box:
=COUNTIF(CustNums,A1)>1
5. Activate the Format... push button and select a background color and/or font color to make the items stand out.

by roc180

Depending on your needs, one option if you just want to know how many times a customer is duplicated, would be to import the Excel sheet into Access and perform a query to get a count by customer. If you would like an example let me know and I can Emali you one.

Good Luck

Neal Martinelli

by dfowler

Thanks for the idea! The first three answers seemed to work better for what I'm trying to do, but this is a good option to know about. Thanks!

This discussion is closed to new replies.