Adding Numbers in Sequence to a Field in Excel

excelAs part of a larger project where I had to delete overlapping polygons in ArcGIS and then give them all a unique number within specific boundaries utilizing attribute information from another layer which it overlapped geographically I figured out this little trick for creating a sequence of numbers in excel based on a run of numbers in another column. I think some examples will help explain better.

I have a list, it is about twenty thousand rows long (not exaggerating), so I will use just a snippet here.

Column B

I want to add a “-01”, “-02”, “-03”, and so forth sequence to the numbers in the list, so that I would have a result of “ND15909720-01”, “ND16009501-01”, “ND16009501-02”, “ND16009501-03” and so one, restarting the last two numbers every time the first set of numbers changed.  I decide the best way to do this is to do formula in a third column (the list above is in the second column, the first column has a different unique identifier) then combine the two together in a fourth column.  I used the formula =IF(B2=B1, “0”&(C1)+1, “01”).  What this does is “If the data in a cell in Column B matches the data above it then put a zero and another number in the destination cell, the “another number” beingthe number above the destination cell plus one.  If data in the cell in Column B doesn’t match then just put “01” in the destination cell.
*An interesting note for you, the equation =IF(B2=B1, “0”&1+(C1), “01”) works just as well.

I can then copy and paste that equation down my whole spreadsheet and Excel changes the cell references in the equation for me, making the next entry =IF(B3=B2, “0”&(C2)+1, “01”) and so on.
This gives me

Column B Column C
ND15909720 01
ND16009501 01
ND16009501 02
ND16009501 03
ND16009501 04
ND16009501 05
ND16009501 06
ND16009501 07
ND16009501 08
ND16009503 01
ND16009503 02
ND16009504 01
ND16009504 02
ND16009504 03
ND16009505 01
ND16009505 02

Now to put it all together with a simple statement of =B2&”-“&C2, what this does is just copy the contents from cell B2 into my destination cell, add a hyphen, then copy the contents from cell C2 after the hyphen.

I then copied the content of Column D and pasted just the values so that the formulas would be replaced with just the results.
So I was able to end with

Column A Column B Column C Column D
17531 ND15909720 01 ND15909720-01
4992 ND16009501 01 ND16009501-01
5678 ND16009501 02 ND16009501-02
5741 ND16009501 03 ND16009501-03
5744 ND16009501 04 ND16009501-04
5825 ND16009501 05 ND16009501-05
5829 ND16009501 06 ND16009501-06
10716 ND16009501 07 ND16009501-07
10717 ND16009501 08 ND16009501-08
14865 ND16009503 01 ND16009503-01
15332 ND16009503 02 ND16009503-02
15337 ND16009504 01 ND16009504-01
15380 ND16009504 02 ND16009504-02
15383 ND16009504 03 ND16009504-03
15485 ND16009505 01 ND16009505-01
15487 ND16009505 02 ND16009505-02


Leave a Reply

%d bloggers like this: