Sequentially numbering entries based on non-sequential criteria

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
image

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

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

@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

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

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

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.

2 Likes