Unique ID formula from reverse date of birth

    Hey all,


    I've been trying to work on a project for my employer.


    We are a member organisation where each membered account is given an internal ID, however, the same person can have multiple accounts.
    We're trying to instigate a Unique ID which is individual to each separate person, use a reversed Date of Birth : YYYYMMDDXX


    The XX suffix is dependent if the same date of birth recurs within the data set, but with a different user's name.


    I've tried using Countif to try and work this out, but it seems to have an issue if a member has more than 3 different accounts.



    Would anyone be able to help? I don't mind if the odd ID requires manual checking, but at the moment, I've got several hundred to review.


    I've included a sample of my data.



    Cheers,


    David

    Re: Unique ID formula from reverse date of birth


    So the same user (with diferant accounts) just get 1 List ID?


    And if the date of birth accours multiply time (with another member) it has to be the suffix 02 and suffix 03 (for the third member)?

    Re: Unique ID formula from reverse date of birth


    These birthdates are the same.


    [TABLE="width: 211"]

    [tr]


    [td]

    22597MohammadAkooji

    [/td]


    [/tr]


    [tr]


    [td]

    22597MohammedAkooji

    [/td]


    [/tr]


    [/TABLE]



    I think that will be a typo.


    Since you don't have any birthdate duplicated you can add 01 to the reversed date.



    To check if a birthdate is duplicate I used Column N => copy paste to the a new sheet.


    After that remove duplicates => data => remove duplicate


    After that a Conditional Format to check if the date is duplicated


    See the attached file.

    Re: Unique ID formula from reverse date of birth


    Why not create the ID by using reverse DOB, the first 2 letters of first name and the first 2 letters of last name?


    The formula for that would be:


    =TEXT(E2,"yyyymmdd")&LEFT(B2,2)&LEFT(C2,2)


    Put that in F2 and copy down.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login