function or sub to dedect doubles

**matrixknow** · 06-19-2007, 08:30 AM

I have a column with employee id's. Sometimes two or more employee ID's are double. I need to dedect if there are ID numbers that appears more then ones. If I found such a number I can copy that.
Has someone an idea or is there a build in function to handle this ?

Please Login or Register  to view this content.

the code should detect :
604168 (appears tree times).

**oldchippy** · 06-19-2007, 08:52 AM

You could try this in B1, and drag it down as far as data in column A

=COUNTIF($A$1:$A$5,A1) adjust the range to your range of numbers. Where there is one, 1 will appear, or 3 in this case

**antoka05** · 06-19-2007, 10:18 AM

See the attached file. I added macro 'Macro1' to do what you ask.

I hope it's what you need.

Regards,
Antonio

**antoka05** · 06-19-2007, 10:19 AM

I'm sorry... I forgot to insert attachment file.

**matrixknow** · 06-19-2007, 10:34 AM

ok, thanks Antonio, the company policies forbit to open the attachment
I check it at home and let you know.

**oldchippy** · 06-19-2007, 10:37 AM

Here's a link you may find useful?

http://www.cpearson.com/excel/duplicat.htm

**matrixknow** · 06-20-2007, 05:06 AM

Please Login or Register  to view this content.

CountIf seems to be a worksheet function. So he can't execute this code.

Antonio, I saw your code, it works fine in your example. I just try to find out how it works.

Please Login or Register  to view this content.

C[-1],RC[-1]) = here you define the range. Is C[-1] the previous column and what is RC[-1] ?

**antoka05** · 06-20-2007, 05:45 AM

The formula use relative references with RC (row and column).

To understand the formula, for example, if I put formula in Range B2 it will appear so:
IF(COUNTIF(A:A;A2)>1;"Appears " & COUNTIF(A:A;A2) & " times";"")

If you start macro recorder, go on cell with formula (f.ex. B2), press 'F2' and then enter, stop macro recorder and go to see what Excel has recorded you will see exactly the formula I applied on the macro.

Regards,
Antonio

**matrixknow** · 06-20-2007, 07:32 AM

I have the idea vba does not allowed you to use the worksheet function COUNTIF. Only if you use vba to past it, use the function in your worksheet.
Because I don't want to run the result of the duplicate entries check in my worksheet I think I can't use the COUNTIF function.
Is there an equivalent function to use in your vba or do I just loop the whole column and use an counter ? Perhaps not the best way ?

**antoka05** · 06-21-2007, 04:33 AM

I'm sorry but I don't understand what you exactly need.

Do you want only maintain no duplicated rows? Or do you prefer only put string 'Appears x times' only at the first occurence of it?

Regards,
Antonio

**matrixknow** · 06-21-2007, 04:43 AM

no problem.
I used COUNTIF succesfully by your suggestion. However this works fine if you paste your formula in your sheet. If you are not allowed to past this in your sheet (e.g user is not allowed to change structure) you can not work with COUNTIF. COUNTIF is a worksheet function.