Continuous Number Generator

I have a data encoding project.

I will input my data in column A (equipment type), and the formulated cell would be in column B. Column B contains a string that starts with the value in column A, and appends an equipment-specific counter to it.

The different equipment types in column A can be in any order. The counter needs to increment within equipment type.

Whenever I input new data in a row in column A, the column B value would be automatically created, incrementing the counter by 1 for that equipment type.

In the example, the list starts with two type A's, whose counters are `001` and `002`. Four rows later, there are more type A's, and their counters increment to `003` and `004`.

In between, in rows 3, 4, and 5, there are types B, D, and C. Each of those has a counter starting at `001`. Farther down, there are second types D and C, and the counter on each is incremented by 1. Then a second and third type B, and those counters are incremented to `002` and `003`.

How can I generate the values in column B?

The formula in B2:

``````=IF(ISBLANK(A2),"",A2&"-"&TEXT(COUNTIF(\$A\$2:A2,A2),"000"))
``````

You can prepopulate the column B formula to an arbitrary large range and it will display a blank cell until you enter a value in column A.

It builds the displayed value by appending to the column A value, the hyphen and then the counter. The TEXT function with "000" format provides the leading zeros as needed for a three-digit counter.

COUNTIF counts the values in column A from the beginning through the current row that match the current column A value.

• Thanks! It works just like expected. Commented Jul 6, 2019 at 0:33

If your intention is to take the final character of the data in column A, then add the number, your best bet is to use a basic formula like:

``````RIGHT(A1,1)&"-"&TEXT(ROW(),"000")
``````

The issue is if you're not using A1, just deduct the rows to reach 1 as the starting number. So if your data begins on row 10, the code should be:

``````RIGHT(A1,1)&"-"&TEXT(ROW()-9,"000")
``````
• Just saw Seth's comment pointing out that you actually want a count of how many you have of the same type, as well as it seeming to number the item. This can be accomplished with a helper column which I can help with if you confirm this is what is required. Commented Jul 2, 2019 at 11:07

Place some value in A1 and

``````x-001
``````

in B1. Then in B2 enter:

``````=IF(A2="","",IF(LEFT(B1,1)="x","y"&RIGHT(B1,4),"x-"&TEXT(1*(RIGHT(B1,3)+1),"000")))
``````

and copy downward:

As you see, the prefix in column B will ping-pong between x and y and the suffix will increment as desired.