Sequentially numbering entries based on non-sequential criteria

[Elijah_Magrane]

Hi I need a formula to create list of sequential numbers based on certain criteria, however, the criteria may be mixed.

For example, based on this list here

this spreadsheet formula works well… =IF(A2<>A1,1,B1+1)

However, my list will not be sorted and the numbers must be sequential. For example my list may contain…

Lot # (this will be my seq number)	Flavour
110094	Almond
110095	Almond
110096	Almond
120056	Orange
1110097	Almond
110098	Almond
120057	Orange
130017	Pistachio

I need a way to sequentially number the entries based on the criteria regardless of the order. Any ideas? I think I will need the MAX expression some how I just need a little help

[LeventK]

@Elijah_Magrane
Provided you have multiple app users, sequential numbering might be cumbersome as it’s possible to create doubles when 2 users try to create a record at the same time.

[Elijah_Magrane]

@LeventK That will never happen as this is for manufacturing on a dedicated line with this app being dedicated for the specific task. Moreover, the record is specific to the criteria. For example, the Almond flavour will generate “11…”, while the orange will generate “12…”. Two flavours will never be produced back to back

[LeventK]

@Elijah_Magrane
OK, but I wanted to mention this:
Let’s say that Almond flavour will generate 11, that’s OK and let’s say the last record in the table for Almond is 110098 (as per your sample table records). What I wanted to mention is; provided a 2 users want to create an Almond record, it’s possible that they both will assign 110099 to their records, where you will expect them to create 110099 and 110100 sequentially.

[Elijah_Magrane]

I appreciate them insight but like I said they’re is no scenario in which two users would be entering the same criteria

[Elijah_Magrane]

SOLVED: I ended up figuring it out. If anyone is in need of a similar expression I ended up using (MAX(Select(Lot Numbers[Lot #],[Flavour]=[_THISROW].[Flavour]))+1)

I of course had to have a previous lot entered of the desired flavour.