# 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

## Files

• 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]

[/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.

## Files

• 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.

• Re: Unique ID formula from reverse date of birth

Thanks for the help!!

## Participate now!

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