Hi all, I am trying to create a unique identifying number to each row in a table within Excel.

The unique number needs to be based on another number within the same row.

The example below shows that the unique id in column A is generated based on the number in column B and incremented according to the repetitions of the same number in column B.

Unique id Number Qty

00001 1 1

00002-1 2 1

00002-2 2 1

00002-3 2 1

00003 3 1

00004-1 4 1

00004-2 4 1Ive tried to make this work with a formula but keep hitting a brick wall.

If anyone knows a way to do this it would be greatly appreciated.

Many thanks,

Mike

Please click on the following line and read the instructions on how to post data in this forum so that the columns line up. Then edit your post using the tips found in those instructions. That we will have a better idea of what data belongs in each column. Thanks!

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

Last post is still confusing: Unique ID Number 00001-1 1 00002-1 2 00002-2 2 00002-3 2 00003-1 3 00004-1 4 00004-2 4 00005-1 5There are three of the number 2 in the Number column, therefore 00002 appears 3 times in the Unique ID column but with an incremental suffix.

This is the perfect time to introduce the concept of a "helper column". Helper columns are often used to store intermediate results that can help simplify or provide input for other formulas. In your case, you need to know how many instances of each number you have so that you can created the incrementing sequence. To obtain that count, we will use a helper column.

For simplicity sake, I'll use Column A as the helper column.

With your "Numbers" in C1:C9, enter this formula in A2 and drag it down to A9.

=COUNTIF($C$2:C2,C2)

Note the use of the dollar signs to lock C2 as the start of the range. You should end up with a table that looks like this:

A B C 1 Unique ID Number 2 1 1 3 1 2 4 2 2 5 3 2 6 1 3 7 1 4 8 2 4 9 1 5You can see that it incremented the count for each number as goes down through A2:A9.

Now in B2 enter this and drag it down:

="0000" & C2 & "-" & A2

With that formula I am assuming that you will never have more than 9 instances of a number. If you will have more than 9, then you need a way to drop one of the leading zeros. One way to do that would be to use an IF function to check the length of the numbers in Column C to determine how many leading zeros to use, e.g.

=IF(LEN(C2)=2,"000"&C2&"-"&A2,"0000"&C2&"-"&A2)

This will use 3 leading zeros for original data with 2 digits and 4 leading zeros for orginal data with 1 digit. You could add more IF clauses for 3 digits, 4, etc.

Note: You can use any column you want as the helper column although I don't suggest using something like Column ZZ if the rest of your sheet is only using e.g. A:D. Using a column way out to the right will make your spreadsheet unnecessarily wide. You can hide the helper column if you don't want to see it.

Let me know if this works for you.

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

Thank you very much for a quick and easy solution to the problem. I extended the IF function to accomodate the longer numbers and all works perfectly. Kind regards,

Mike

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History