Generate next IDs based on existing list

[guillaume0314]

Hello guys:

I have a list of models which consists of a code (alphabetical) and an ID (which start from 100), hyphen-separated.

I need to calculate the immediate next ID to assign by code following this rule:
- if the model exists, the first ID which is missing in the sequence starting at 100, incremented by 1 (if there are existing models with 100,101,103, then 102 is missing, so 102 shall be returned)
- if the model does not exist, shall return 100

There are duplicates models which is normal. We need a formula we can drag down.

Any ideas? Thanks.

generate next id.jpg

[Glenn Kennedy]

Try:

=LET(A,$C$5:$C$41,B,RIGHT(FILTER(A,(A<>"")*(LEFT(A,LEN(F4))=F4)),3)+0,C,ISNA(MATCH(SEQUENCE(100,,100),B,0))*SEQUENCE(100),IFERROR(100+MIN(FILTER(C,C>0))-1,100))

now to try to make the ugly look prettier...

[Glenn Kennedy]

V2...

and a Q.

=IFERROR(BYROW(F4:F7,LAMBDA(x,LET(A,C4:C100,B,SEQUENCE(100),C,ISNA(MATCH(99+B,RIGHT(FILTER(A,(A<>"")*(LEFT(A,LEN(x))=x)),3)+0,0))*B,100+MIN(FILTER(C,C>0))-1))),100)

Is there an anticipated MAXIMUM length for the numerical portion? >999 for example??

[Glenn Kennedy]

V3, works for serial numbers up to 9999

Please Login or Register  to view this content.

Adjust the bits in RED to suit, the 10000 if more than 9999 numbers are needed.

[Phuocam]

Or try this:

=AGGREGATE(15,6,(SEQUENCE(900)+99)/(COUNTIF($C$4:$C$22,F4&"-"&(SEQUENCE(900)+99))=0),1)

[sandy666]

option with Power Query

Result

Ag-100

Ag-101

Ag-102

Ag-103

Ag-104

An-100

An-101

An-102

MWc-100

ZrO-100

ZrO-101

ZrO-102

ZrO-103

ZrO-104

ZrO-105

ZrO-106

ZrO-107

ZrO-108

ZrO-109

ZrO-110

ZrO-111

ZrO-112

ZrO-113

ZrO-114

ZrO-115

ZrO-116

ZrO-117

ZrO-118

ZrO-119

ZrO-120

[guillaume0314]

Thank you guys. I will look into the details of your proposals within the next hours.

@Glenn - there is theoretically no max, although technically 999 should be enough. I understand this parameter has to be manually adjusted.

Question, just while viewing the formula without playing with it yet - it is not possible to generate a "dynamic" SEQUENCE? If your LET filter the table by the requested "model", you would know the length of your array (5 rows for example) and thus generate the genuine appropriate sequence for 5 serial numbers ( {100;101;102;103;104} and returning the first one missing - instead of pulling 10,000 serial numbers - the issue with the MAX being lifted - does this make sense or am I wrong?

[Glenn Kennedy]

Yes and no. It's possible... but muddling around working out, code by code, the maximum value used... will be slower than generating a simple sequence.

[guillaume0314]

Thank you guys it works.