Formula to determine if duplicate values exist in a single column (excluding empty cells)

kwoltman

New Member
Joined
Jun 29, 2012
Messages
25
1 workbook, 2 worksheets (or tabs)
On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2

Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).

One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.

I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.

The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.

I hope all of this makes sense and thanks in advance for the help.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You can use the match function in conjunction with the conditional formatting. I would use a named range for the Unique IDs set on tab 2. Then on tab 1, I would conditionally format column A if match is number turn the cell red, if match is error(no match found) leave the formatting or don't even put this condition in there.
 
Upvote 0
You can use the match function in conjunction with the conditional formatting. I would use a named range for the Unique IDs set on tab 2. Then on tab 1, I would conditionally format column A if match is number turn the cell red, if match is error(no match found) leave the formatting or don't even put this condition in there.

I haven't used the MATCH function, but the help text within Excel says that Match will return the position - I don't think that'll help me, but I certainly could be wrong:) - could you explain in further detail?

Named Range - if my range is going to grow, how can I go about naming a definitive range?

Thanks again
 
Upvote 0
This CSE formula =(MAX(COUNTIF(A1:A99999, A1:A99999)*Len(A1:A99999))>1) will give you True/False "is there any non-blank duplicate in A1:A99999"

If you apply the Conditional Format =(MATCH(A1, A:A, 0) <> ROW(A1)) to A1 and duplicate the CF to all the cells of column A, any duplicated value will be highlighted.
 
Upvote 0
This CSE formula =(MAX(COUNTIF(A1:A99999, A1:A99999)*Len(A1:A99999))>1) will give you True/False "is there any non-blank duplicate in A1:A99999"

If you apply the Conditional Format =(MATCH(A1, A:A, 0) <> ROW(A1)) to A1 and duplicate the CF to all the cells of column A, any duplicated value will be highlighted.

Thanks Mike. I input the MAX formula and I double and triple checked it, but it keeps telling me "You've entered too few arguments for this function" - any ideas?

Here's my exact formula: =(MAX(COUNTIF('Asset Management'!A1:A1048576)*Len('Asset Management'!A1:A1048576))>1)

(I also discovered "Format only unique or duplicate values" within the Conditional Formatting Rules, so now it only colors those that are truly duplicates and seems to ignore the empty cells.)
 
Upvote 0
The match function only returns a value if there is an exact match. If there is not, it returns N/A. Therefore if isnumber(match...) is true, you have a duplicate.

As for the named range, you can rename a range via vba and associate it with the worksheet and then associate it with the action, On Change. I do a lot of these on open or on close, where I update my information when the user leaves the sheet. This is mostly because the person updating the list is different than the person updating the data itself.
 
Upvote 0
The match function only returns a value if there is an exact match. If there is not, it returns N/A. Therefore if isnumber(match...) is true, you have a duplicate.

As for the named range, you can rename a range via vba and associate it with the worksheet and then associate it with the action, On Change. I do a lot of these on open or on close, where I update my information when the user leaves the sheet. This is mostly because the person updating the list is different than the person updating the data itself.

My apologies VAVS - I'm a novice and I don't follow how to setup what you're suggesting. With Mike's formula I'm very close, but it's not working yet (I think because COUNTIF requires Range and the Criteria - and I don't see a criteria in Mike's example. Anyone know how to make Mike's MAX formula work?
 
Upvote 0
Thanks Mike. I input the MAX formula and I double and triple checked it, but it keeps telling me "You've entered too few arguments for this function" - any ideas?

Here's my exact formula: =(MAX(COUNTIF('Asset Management'!A1:A1048576)*Len('Asset Management'!A1:A1048576))>1)

(I also discovered "Format only unique or duplicate values" within the Conditional Formatting Rules, so now it only colors those that are truly duplicates and seems to ignore the empty cells.)

Your formula does not have both arguments. Countif requires range and criteria. 'Asset Management'!A1:A0485576 is either the range or criteria. In Mike's original he has A1:a99999 as both the range and the criteria.
 
Upvote 0
Yes, the CountIf in my formulation has the same array as both arguments. Your formula should be

=(MAX(COUNTIF('Asset Management'!A1:A1048576, A1:A1048576)*Len('Asset Management'!A1:A1048576))>1)

entered with Ctrl-Shift-Enter (Cmd+Return for Mac)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top