macro for finding duplicate person names

[Rockerdo]

Is there a macro that I can put in to find duplicate values (Excel 2010)? Using the conditional format is not working well since it is highlighting all the cells even when they are not duplicates.

We have a large datasheet that is in a shared folder. I've made the spreadsheet a data sheet so they can easily use the form fields to fill in and it will autopopulate to the next line. This works really well. However, we are finding that sometimes that person was already entered so they are in there twice. There are several fields to enter but the one that we want to look for duplicates in is in column C where they type the persons name that was trained

I thought a macro would be better then I could just run it from time to time and highlight all the cells in that column that are duplicative so they can correct them.

 

[Joe4]

Using the conditional format is not working well since it is highlighting all the cells even when they are not duplicates.

That doesn't mean that Conditional Formatting will not work, it simple means that you probably just aren't using it correctly.
You could use a Conditional Formatting formula like this:

=countif(C:C,C1)>1

which counts how many cells in column C have a value that matches the cell in that particular row.

 

[Rockerdo]

I have that but every single cell highlights. There are two names in each cell (first and last name) so maybe that is the issue. That is why I thought a Macro would work. Right now there are 600 entries on this form and it will only grow each week/month so not having to do a bunch of sorting we thought having it highlight the duplicates would make it easier.

So line 1 I have John Blankenship and maybe he appears again on line 208. I want his name to highlight so that I know he has been entered twice. I don't want it to auto delete as they need to evaluate if it is appropriate or maybe it is another person with the same name. Just want the highlight to make finding the duplicates a lot easier

 

[Joe4]

There are two names in each cell (first and last name) so maybe that is the issue.

That doesn't matter. It is counting the number of cells that exactly match the one in that row.

So if you had:

John Doe
John Adams
John Doe
Mark Adams

The two "John Doe" records would be highlighted, but the others would not be.

Maybe you have other failed Conditional Formatting formula in there interfering with it. I would recommend deleting all the existing Conditional Formatting on the sheet and start over.
Highlight the whole range you want to apply it to, and then when you enter in the formula:

=countif(C:C,C1)>1

Make that that the row reference is the same as the first row in your selected range to highlight.
For example, if your data starts on row 2, so you are highlighting cells C2 on down, the adjust your formula to this:

=countif(C:C,C2)>1

Excel is smart enough to adjust all the formulas below that.

Also, be sure you are using "> 1" and not "> 0 " (which would select all rows).

Lastly, if you are applying the formatting to the whole row and not just column C, you will need to add absolute column references, i.e.

=countif($C:$C,$C1)>1