PDA

View Full Version : Need help with finding duplicates per column



davenavarro
08-23-2007, 01:15 PM
'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:
look at a single column,
flag any duplicates in that column only, and
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
08-23-2007, 01:26 PM
You can select all the columns and add the CF in one action.

davenavarro
08-23-2007, 01:31 PM
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:
Highlight Column
Select Format / Conditional Formatting
Change drop down from Cell Value Is to Formula Is
In the empty formula field, enter =COUNTIF($X:$X,X2)>1, where X is the column letter
Click the Format button
Click the Patterns tab
Select the color you want to flag it as
Click OK to close the Format Cells dialog
Click OK to close the Conditional Formatting dialog
Enjoy the showIs there a generic-ish formula I can put in there?

- Dave

Bob Phillips
08-23-2007, 02:49 PM
The formula would be

=COUNTIF(X:X,X2)>1

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

Bob Phillips
08-23-2007, 02:51 PM
BTW, I think the formula also assumes you are starting at row 2, if not the 2 should be the start row as well.