Need help with finding duplicates per column

[davenavarro]

'ello -

I've been trying to figure out a way to to flag duplicate entries per column with VBA. I've made it work with conditional formatting, but I don't want to have to manually add the formula into each column.

The XLS in question is a spreadsheet where each column represents a
calendar week and the rows have developer's name in them like this:

(Hmm ... I can't post a link because this is my first post, but if you pull out the spaces and go to this address you'll see a pic of my XLS:
i225. photobucket. com/albums/dd294/kidogo99/chart.jpg

What I'm Looking For
I want to have something that will:

1. look at a single column,
2. flag any duplicates in that column only, and
3. move on to the next column (stopping when a blank column is reached).

If I do conditional formatting (to turn duplicates in a column red, for example), I have to manually add the formula to each column. I'm handing this off to my supervisor, so I'd rather have something that was automatic.

I've found a few sites that tell me how to flag cells in a range, but I don't know how to get it to move from column to column (only checking for duplicates in the current column). And since I know squat about VBA, I'm stuck as far as it goes to tweaking the code I found in the above referenced page.

Any help would be supremely appreciated.

- Dave

[Bob Phillips]

You can select all the columns and add the CF in one action.

[davenavarro]

Can I? That would be great. How would I write the formula so that it would be applicable to all columns?

Currently, this is what I'm doing:

1. Highlight Column
2. Select Format / Conditional Formatting
3. Change drop down from Cell Value Is to Formula Is
   1. In the empty formula field, enter =COUNTIF($X:$X,X2)>1, where X is the column letter
4. Click the Format button
   1. Click the Patterns tab
   2. Select the color you want to flag it as
   3. Click OK to close the Format Cells dialog
5. Click OK to close the Conditional Formatting dialog
6. Enjoy the show

Is there a generic-ish formula I can put in there?

- Dave

[Bob Phillips]

The formula would be

=COUNTIF(X:X,X2)>1

where X is the last column selected (that bit is VERY important)

[Bob Phillips]

BTW, I think the formula also assumes you are starting at row 2, if not the 2 should be the start row as well.