Solved How to add incrementing number to a row based on a value

February 11, 2014 at 17:22:41
Specs: Windows 7
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 1

Ive 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,

See More: How to add incrementing number to a row based on a value

Report •

February 11, 2014 at 18:41:34
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.

Report •

February 11, 2014 at 20:19:25
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	         5

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

Report •

February 12, 2014 at 07:51:10
✔ Best Answer
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.


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          	      5

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


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.

Report •

Related Solutions

February 16, 2014 at 15:56:20
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,

Report •

Ask Question