Results 1 to 10 of 10

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

This is a discussion on Formula to determine if duplicate values exist in a single column (excluding empty cells) within the Excel Questions forums, part of the Question Forums category; 1 workbook, 2 worksheets (or tabs) On tab 1, I want a formula/alert that tells the user if any duplicate ...

  1. #1
    New Member
    Join Date
    Jun 2012
    Posts
    21

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

    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.

  2. #2
    Board Regular
    Join Date
    Jul 2004
    Posts
    495

    Default Re: Formula to determine if duplicate values exist in a single column (excluding empty 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.
    Tim Vavra

    Using Excel 2003, 2007 and 2010
    Attempting to use Excel for Mac

    As to that which exists, search deeply for you can be anything you wish.

  3. #3
    New Member
    Join Date
    Jun 2012
    Posts
    21

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

    Quote Originally Posted by vavs View Post
    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

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,096

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

    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.

  5. #5
    New Member
    Join Date
    Jun 2012
    Posts
    21

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

    Quote Originally Posted by mikerickson View Post
    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.)

  6. #6
    Board Regular
    Join Date
    Jul 2004
    Posts
    495

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

    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.
    Tim Vavra

    Using Excel 2003, 2007 and 2010
    Attempting to use Excel for Mac

    As to that which exists, search deeply for you can be anything you wish.

  7. #7
    New Member
    Join Date
    Jun 2012
    Posts
    21

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

    Quote Originally Posted by vavs View Post
    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?

  8. #8
    Board Regular
    Join Date
    Jul 2004
    Posts
    495

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

    Quote Originally Posted by kwoltman View Post
    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.
    Tim Vavra

    Using Excel 2003, 2007 and 2010
    Attempting to use Excel for Mac

    As to that which exists, search deeply for you can be anything you wish.

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,096

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

    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)

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,096

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

    upon further reflection the last term should be SIGN(LEN(...))

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com