Use formula to identify duplicates

**lizzae** · 09-10-2009, 11:36 AM

I have a large list of data.
There is a column that contains a set of numbers that appear twice.
I wanted to give the first occurence of the number a 1 and the second occurence of the number a 2 and then I could easily sort the data and delete all the second occurences of the name by deleting anything with a 2 value.

For example

Mary Smith 1
John Smith 1
Cindy Apple 2
Margaret Apple 2
Bob Hope 3
Mike Hope 3

I'd like to create a row that would identify Mary Cindy and Bob with a 1 and John Margaret and Mike with a 2

Thanks
L

**NBVC** · 09-10-2009, 11:39 AM

=countif($B$2:$B2,B2) copied down

assuming the items are in separate columns and column of interest starts at B2

**vorabha** · 09-10-2009, 11:42 AM

try this

=IF(B1=B2,"false",A2)

**NBVC** · 09-10-2009, 01:15 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthr...=415268&page=2