# 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 |