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