Find duplicate values in Excel 3

[jsgs]

Hello everybody!

I have a column with 900 User Ids. Is there any function in Excel that allows me to find all duplicate Ids there are in the list and point me to them? (Using Excel 03)

 

[SkipVought]

Hi,

You could sort them. Use COUNTIF to count the occurences.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

If you want to get a list of unique IDs, have a look at Data > Filter > Advanced Filter > Unique Values.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I all depends on what you REALLY ultimately need to do with the duplicates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

agreed

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JPaules]

There is an excellent Excel add-in that can do that and scads of other tasks. ASAP Utilities - you won't be disappointed with it. I have sound that it allows me to do so many tasks in Excel MUCH more easily than I would without it. And it's free. Gotta love that part!

--
JP

 

[mintjulep]

Sort them.

Then (assuming names are in column A starting in row 1)

In B2 enter:

=if(A2=B2,"Duplicate","")

Copy down.

This of course will not flag duplicates that are not exactly duplicate.

 

[Gavona]

Another option to get a unique list is a pivot table, using the Count function in the data field and sorting on the value in that field.

TIP: Using the Advanced filter option: if you create the unique list in a new location you can then use that list as the criteria for a further advanced filter (filter in-place) on the original data - thus displaying just the records where there are duplicates.

The advanced filter option has the advantage of being able to generate a unique list of column combinations. Not what you are looking for here but a very useful tool.

Gavin

 

[jsgs]

SkipVought:

I will have to edit the duplicate IDs so as to make them unique.

mintjulep:

Nice trick. Thanks. I've tried it but there are 2 catches:
1) The User Ids were not in alphabetical order. So if there are let us say 5 occurrences of one user id all scattered among the list it won't flag them as duplicates.
2) So i sorted the list in alphabetical order. Now the 5 occurrences of the user id will be together. But it will flag only 3 as duplicates, the 3 in the middle of the 5 user ids.

JPaules:

I've downloaded the utility and installed it. Nice app. But there's no function in that to check for duplicate values.

 

[mintjulep]

Sorry, typo in my first reply.

=IF(A1=A2,"duplicate","")

With a list in alphabetical order that will flag all duplicates.

 

[JPaules]

Yes, there is a duplicate values checker. ;-)

Select - Conditional select cells - Based on values tab - deuplicate values.

There are soooo many great options there that you're going to want to use the User Guide. There's no way you can memorize all of the functions. I use several of them often so I can find them quickly. Others drive me to the guide everytime.

(Wait till you see that you can create an Excel file directory of any folder on your computer. Love that one!)

--
JP

 

[Salut39]

I use this formula:

=ISNUMBER(MATCH(A1,OFFSET($A$1,0,0,ROW(A1)-1,1),0))

add it to a conditional format and it will highlight all duplicates for you.

Yuri

 

[jsgs]

mintjulep :

I had already corrected the mistake when i tried it. Thanks again!

JPaules:

Found it. You are right. That's a great little program.

Salut39:
The userids are text values so i suppose the Isnumber function is not applicable here.

Thanks to all for your help.