#HOW TO FIND MISSING "BILL NUMBER" IN MY LIST ??? ( MY FORMAT : EXP/001/2021 )

[krunal123]

This Formula use , Missing Date find perfect , but we need Find "Bill Number " , Because my file Large Amount of Bill Numbers ( May be 3000 Bill Number )


=SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)),$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2),ROW(A1))
( This formula only work date format.)
.
.
Pleased Help WE NEED FIND MISSING " BILL NUMBER " ( My Format was EXP/001/20-21 )

 

[Phuoc]

Try this:

=FILTER(left(E2;4)&text(row(INDIRECT(mid(E2;5;3)&":"&mid(E3;5;3)));"000")&right(E2;6);
countif(A3:A7;left(E2;4)&text(row(INDIRECT(mid(E2;5;3)&":"&mid(E3;5;3)));"000")&right(E2;6))=0)

 

[Peter_SSs]

Does this do what you want?

21 09 07.xlsm
	B	C	D	E
1
2				EXP/001/20-21
3	EXP/001/20-21			EXP/009/20-21
4	EXP/002/20-21
5	EXP/005/20-21		Missing
6	EXP/008/20-21		EXP/003/20-21
7	EXP/009/20-21		EXP/004/20-21
8			EXP/006/20-21
9			EXP/007/20-21
10
Missing

Cell Formulas
Range	Formula
D6:D9	D6	=LET(strt,MID(E2,5,3),seq,"EXP/"&TEXT(SEQUENCE(MID(E3,5,3)-strt+1,,strt),"000")&"/20-21",FILTER(seq,ISNA(MATCH(seq,B3:B7,0)),""))
Dynamic array formulas.

 

[Peter_SSs]

.. or without hard-coding the prefix & suffix

21 09 07.xlsm
	B	C	D	E
1
2				EXP/001/20-21
3	EXP/001/20-21			EXP/009/20-21
4	EXP/002/20-21
5	EXP/005/20-21		Missing
6	EXP/008/20-21		EXP/003/20-21
7	EXP/009/20-21		EXP/004/20-21
8			EXP/006/20-21
9			EXP/007/20-21
10
Missing

Cell Formulas
Range	Formula
D6:D9	D6	=LET(strt,MID(E2,5,3),seq,REPLACE(E2,5,3,TEXT(SEQUENCE(MID(E3,5,3)-strt+1,,strt),"000")),FILTER(seq,ISNA(MATCH(seq,B3:B7,0))))
Dynamic array formulas.