Adding Numbers in Sequence to a Field in Excel
As 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 |
ND15909720 |
ND16009501 |
ND16009501 |
ND16009501 |
ND16009501 |
ND16009501 |
ND16009501 |
ND16009501 |
ND16009501 |
ND16009503 |
ND16009503 |
ND16009504 |
ND16009504 |
ND16009504 |
ND16009505 |
ND16009505 |
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 |