Excel function for sequential numbering with the same reference

What is the function for sequential numbering with the same reference? It is really hard to do it manually. Any help will be greatly appreciated. Photo attached is the sample. Thank you

Assuming I understand correctly, the hardest part is that column B is text and Excel does not know how to add 1 to text. Again, assuming I understand, here's how I might approach this:

1) Enter 1205 in B2
2) In B3, enter the formula =IF(A2=A3,B2,B2+1)
3a) If the number is acceptable as an output, I can add the "IN " using number formatting. A custom number format code like "IN "General or "IN "0 (custom number code help file: https://support.microsoft.com/en-us/...rs=en-us&ad=us ) Be sure to understand that the IN is only part of the displayed value, but it is not part of the actual cell value (sometimes spreadsheets cause confusion when a number format causes the display to be very different from the cell's actual value).
3b) If the final result must be text, then I would do the concatenation to text in a separate column. In C2, enter ="IN "&B2. Copy/paste/fill down as far as needed. In this case, be clear that the value in column B is a number and the value in column C is text. Column B will be more useful for numeric manipulations (like adding 1 when needed).

Does something like that help?

So the if Current Row value is equal to last row value then repeat last ID approach is valid... but this is highly dependent on the data be sorted correctly at all times...

And we are on row 2 but you are already on ID 1205... How are you getting this value to start with... does the receipt belong to another data set that contains such an ID?

I think what has been offered will generate next ID correctly provided your data is sorted appropriately and there is no chance that it will be sorted again... I say this because the ID is moot if you have to keep sorting... one day someone may put receipt 10124 500 rows down...and it will get a new ID rather than find the previous ID... to which you may want to error trap yourself and colleagues alike.

B2=VLOOKUP($A2,$A$1:$B1,2,False)

Now this will find previous entries above current record and return the same record... but if it does not exist... ERROR "#N/A"

So Trap that, what do we want to do when that happens? well we want it to get the MAX of the ID's that have been used and add 1 to it... But this requires a bit of MrShorty's trick in which you visually represent the IN through a custom format so we can get the max... OR you put a helper column in that has the numeric portion of the text only..

MrShortys use of formats will allow this to work
B2 = IFERROR(VLOOKUP($A2,$A$1:$B1,2,False),MAX($B$1:$B1)+1)

Now wait a minute... it needs one more error trap... because the FIRST ID will be an error... it wont find a previous entry and there is no MAX, so wrap that little guy in one more iferror to set 1 as the first ID

B2 = IFERROR(IFERROR(VLOOKUP($A2,$A$1:$B1,2,False),MAX($B$1:$B1)+1),1)


If you make a "Helper" Column you would need to sort out your way of getting the numeric portion of the string...Being that it is a fixed format a simple

=RIGHT(B2,LEN(B2)-3)

Would do it. Why -3? well we know that the first 3 CHAR in your string is "IN " and that space counts so you want everything to the right of the first 3 characters...

You could go over the top and strip the string of everything that is not text

=TEXTJOIN("",TRUE,IFERROR((MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)*1),""))

Any way that you do it... you will need to update the reference of MAX($B$1:$B1) to be the column you drop the HELPER column in.