Number increment in Google Sheets formula

Question

In a Google Sheets database, I have a formula which I have built in order to allocate a reference number to a series of companies.

Each company should have its unique number in the form of RET00XX where XX will represent the unique company number. I would like these numbers to be sequential, starting on 1 and going on +1 after that. Whenever a new company is inserted in the database, the formula should be able to attribute it a reference number. It should also be able to verify if the company already exists in the database and, if so, automatically attribute it the company's unique reference number, instead of creating a new one.

The company names are in cells of column B.

This is the formula I have built (an example of the one in row 2):

=ARRAYFORMULA(IF($B2<>"",IF((COUNTIF($B$1:$B1,$B2)>0),INDEX($A$1:$R2,MATCH($B2,$B$1:$B1,0),12),CONCATENATE("RET00",ROW($B2))),""))

The steps it takes are:

1. It verifies that column B in the correspondent row is not empty;
2. With the COUNTIF function, verifies that the company does not exist in any of the previous rows;
3. If the company does exist, it attributes the correspondent reference number through the INDEX function;
4. If the company doesn't exist, it attributes the company a new reference number with the CONCATENATE and ROW functions.

The formula is largely working, although there are some problems. Users adding to this database have the habit of adding entries by inserting rows in the middle of the database. This makes it so, due to the way the formula is built, that company unique reference codes change each time that happens. I believe this is partially due to the fact that I use a ROW function.
Also, given that new rows are inserted in the middle of the database, the formula should be able to verify is the company already exists not only by looping through all previous rows but rather through all rows (if a new row is inserted, the formula will only verify previous rows, when the company could be in the rows after the new one).

How can I attribute sequential numbers in a formula without reference to ROW? Also, how can I make sure that the spreadsheet verifies for all rows of column B instead of just the ones before the inserted row?

Answer 1

apply this formula in your sheets,

=ArrayFormula(if(B2:B<>"",row(A2:A)-1,""))

More information regarding this please visit this
link : https://infoinspired.com/google-docs/spreadsheet/auto-serial-numbering-in-google-sheets/

Answer 2

Solution that is independent of starting row number

These examples will allow you to generate incrementing values in your formulas.

Incrementing integers, zero based: The values will be: 0,1,2,3, etc.
Note: The address "$A$2" represents the cell of your top row. It should be changed to whatever cell your actual top row is. The nice thing about this method is it it will not break if you insert new rows above the start position of your formula.

=(ROW()-ROW($A$2))

Integers, one based: The values will be: 1,2,3,4, etc.

=(ROW()-ROW($A$2) + 1)

Dates: The values will be: 2000-01-01,2000-01-02,2000-01-03, etc.

=Date(2000,1,1) + (ROW()-ROW($A$2))

All Even Numbers: The values will be: 0,2,4, etc.

=(ROW()-ROW($A$2) * 2