Forum Discussion
Formula Finding missing sequence
Hi all,
I have been searching the internet for days but it looks like many formulas dont work on our excel...
But maybe someone can help me out.
We need a formula to find missing invoices.
Does someone has a good formula for this?
I first had this =RIGHT(A2;4)+1<>RIGHT(A2;4)+0
Didnt work it only says true on every number.
For example:
We got invoices start with 1000112
then goes to 1000114
1000113 is missing but my formula doesnt says so.
Can someone help me out?
2 Replies
An alternative could be conditional formatting. You can compare a list of all invoices with the list of available invoices. To do this you can apply:
Conditional formatting -> Highlight cell rules -> Duplicate Values -> Unique
If you have FILTER, you might try:
=LET(seq,SEQUENCE(1+MAX(invoice)-MIN(invoice),,MIN(invoice)),FILTER(seq,ISERROR(XMATCH(seq,invoice))))
