I am currently trying to make a spreadsheet where I assign a ID to every set of data which I have. The ID is the first 5 letters of the last name followed by the first 2 letters of the first name and a two digit number (usually 01 but can differ in order to account for duplicates). I was wondering if there was some sort of a shortcut I could use to do this instead of manually entering every single one. Below is an example of what I'm trying to do: Last Name First Name ID

Johnson Michael johnsmi01Thanks in advance for your help!

First, a posting tip: If you click on the blue line at the bottom of this post, you can read the instructions on how to post data in this forum so that the format is retained. It may take some trial and error via the Preview Follow Up button, as explained in the instructions, but it can be done. To make it easier for us to understand the layout of your data, it should look like this:

A B C 1 Last Name First Name ID 2 Johnson Michael johnsmi01OK, the easy part is the creation of the basic string e.g. johnsmi01. The harder part is dealing with the duplicates.

This will get you your basic string:

=LOWER(LEFT(A2,5) & LEFT(B2,2)) & "01"

The LEFT function extracts the specified number of characters from the referenced cell. The Concatenation operator (&) combines the results of the functions into one string. The LOWER function converts all letters to lower case.

However, you need to increment the digit at the end to account for duplicates, triplicates, etc. One method for doing that is to use a Helper Column so that you can

counthow many times any given "partial ID" (e.g. johnsmi) occurs.Note: This method will work for up to 9 occurrences of a partial ID. If you will have more than 9, then things get a little more complicated, but it can still be done. Let us know if you need more than 9.

OK, so let's say you have the set up described above. You need to create a Helper Column, which you can then hide so that it doesn't show. I'll use Column A, but you can use any unused column that you want, the concept is the same.

1 - Insert a new Column A. this will move your names to Columns B & C

2 - Place this formula in A2 and drag it down:=LOWER(LEFT(B2,5)&LEFT(C2,2))&"0"

This will give you your partial ID without the final digit, e.g. johnsmi0

3 - In D2, enter this formula and drag it down:

=A2&COUNTIF($A$2:A2,A2)

Note the placement of the two $, they are required to lock the beginning of the range that we are counting.

This will pickup the partial ID in Column A and append a digit representing the number of times that partial ID appears in Column A.

Let us know if you have any questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Here is a modification of the ideas that DerbyDad03

gave you:With your data like this:

A B C D 1) Last Name First ID Complete ID 2) Johnson Michael 3) Public John 4) Smith Alfred 5) Nee Michael

In Column C enter the formula:=LOWER(LEFT(A2,5)&LEFT(B2,2))

In column D enter the formula:

=C2&TEXT(COUNTIF(C$2:$C2,C2),"00")

You should end up with something like:

A B C D 1) Last Name First ID Complete ID 2) Johnson Michael johnsmi johnsmi01 3) public John publijo publijo01 4) Smith Alfred smithal smithal01 5) Nee Michael neemi neemi01

This should work for 99 duplictes and if you wish

you can hide colomn C.MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll