drockdamian
New Member
- Joined
- Dec 13, 2021
- Messages
- 11
- Office Version
- 365
- 2021
- Platform
- Windows
Hello All,
I have a sequence of invoice numbers that I need to find AND list the missing invoice numbers. The beginning invoice number is 97680 and the last one is 198698. The difference between these two numbers is 101,018, however, a count of the range is 99062 (missing 1,956 invoice numbers in the sequence). I have searched the internet and keep seeing this formula:
=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135,
MIN($A$1:$A$135)+ROW($1:$135)-1)=0,
MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")
So it works for this limited range but when I try to expand the range to $A$1:$A$99062 to cover my range it does not work. Any ideas here would be helpful!
I have a sequence of invoice numbers that I need to find AND list the missing invoice numbers. The beginning invoice number is 97680 and the last one is 198698. The difference between these two numbers is 101,018, however, a count of the range is 99062 (missing 1,956 invoice numbers in the sequence). I have searched the internet and keep seeing this formula:
=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135,
MIN($A$1:$A$135)+ROW($1:$135)-1)=0,
MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")
So it works for this limited range but when I try to expand the range to $A$1:$A$99062 to cover my range it does not work. Any ideas here would be helpful!