+ Reply to Thread
Results 1 to 4 of 4

Unique ID Verification

  1. #1
    Registered User
    Join Date
    08-17-2008
    Location
    LA
    Posts
    22

    Unique ID Verification

    Hello All,

    I am working with a set of data with 36k+ rows.

    Each row has a name and an ID number assigned. The names appear multiple times.

    example:
    John 1
    John 1
    John 1
    Jack 2
    Jack 2
    Tom 3
    Bill 4
    Bill 4
    Bill 4
    Bill 4
    Bill 4
    Bill 4

    I am trying to verify my data to ensure that each name is only assigned an ID once. I do not have any duplicate people as in John is John. Another John that shows up MUST be the person.
    John 1
    John 1
    John 1
    Jack 2
    Jack 2
    Tom 3
    Bill 4
    Bill 5
    Bill 4

    So from the data set above, Bill is assigned ID#4 and ID#5. Is there a formula or something on excel that when that occurs, it's highlighted or some text can be used in a formula? That way I can filter this third column to find the issue.

    John 1 N
    John 1 N
    John 1 N
    Jack 2 N
    Jack 2 N
    Tom 3 N
    Bill 4 Y
    Bill 5 Y
    Bill 4 Y

    OR

    John 1 N
    John 1 N
    John 1 N
    Jack 2 N
    Jack 2 N
    Tom 3 N
    Bill 4 N
    Bill 5 Y
    Bill 4 N

    Either way works, just a way for me to easily and quickly identify duplicate ID's assigned to someone. Thanks in advance!
    Last edited by jdot; 05-01-2013 at 08:59 PM. Reason: Solved

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Unique ID Verification

    Hi

    Say your data is in the range A1:B9, then try
    C1: =COUNTIF($A$1:$A$9,A1)=COUNTIFS($A$1:$A$9,A1,$B$1:$B$9,B1)
    Copy down to C9, and do a search on the FALSE

    HTH

    rylo

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,140

    Re: Unique ID Verification

    hi jdot. another alternative, assuming data starts from A2:B10
    =COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"<>"&B2)>0

    or if you are using Excel 2003 & below (please update your excel version in your profile. would be useful for us to know):
    SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10<>B2))>0

    i wouldnt go with your 2nd method because what if Bill with ID 4 & 5 appeared twice each. which is "y" & "n"?

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    08-17-2008
    Location
    LA
    Posts
    22

    Re: Unique ID Verification

    rylo, I went with your suggestion since it showed up first. It slowed my excel down at first, but when it was done calculating, it was exactly what I was looking for! Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1