List Missing Number in Large Sequence

[drockdamian]

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!

 

[HongRu]

Try this.
You have to press "Ctrl+Shift+Enter" instead of "Enter" only.

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$99062,
MIN($A$1:$A$99062 )+ROW($1:$99062 )-1)=0,
MIN($A$1:$A$99062 )+ROW($1:$99062 )-1),ROW(A1)),"")

The code is beautiful and awesome.

 

[jasonb75]

Which version of excel are you using?

This comes to mind as something that might work but I haven't been able to test it.

=LET(s,SEQUENCE(101018,97680),FILTER(s,MATCH(s,A:A)<s))

 

[drockdamian]

Try this.
You have to press "Ctrl+Shift+Enter" instead of "Enter" only.

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$99062,
MIN($A$1:$A$99062 )+ROW($1:$99062 )-1)=0,
MIN($A$1:$A$99062 )+ROW($1:$99062 )-1),ROW(A1)),"")

The code is beautiful and awesome.

Hi HongRu, I have tried the ctrl shift enter but it still returns a zero. However when I leave the formula as I found up to range A1:135 it works. I can’t figure out what I am doing wrong so that it does not work once I expand the range.

 

[drockdamian]

Which version of excel are you using?

This comes to mind as something that might work but I haven't been able to test it.

=LET(s,SEQUENCE(101018,97680),FILTER(s,MATCH(s,A:A)<s))

Thanks Jasonb75. I am using Excel 2019. I will have to try this!

 

[jasonb75]

With 2019 you will not have some of the functions that I have used.

I'll have a look at an alternative for you shortly when I have my laptop to test it on.

 

[jasonb75]

Trying a couple of things in excel, I'm wondering if it might be too much to process with the original formula and the full range of numbers, I've been noticing some delay when I tried anything using countifs.

This works for me in office 365 using methods that are compatible with older versions of excel. Hopefully it will work for you as well.

Firstly, you need to create a named range (it can be done without but this improves performance with a larger range of numbers).

Go to the Formulas tab on the excel ribbon, then click Name Manager, followed by New and enter the following into the boxes.
You will need to change the sheet name to match yours, and the references to the data column if different.

Name: rowarray
Scope: Workbook
Refers to: =ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MAX(Sheet1!$A:$A)))

Then enter this formula into your sheet and array confirm it with Ctrl Shift Enter before filling down as needed.

=IFERROR(AGGREGATE(15,6,rowarray/(LOOKUP(N(IF({1},rowarray)),A:A)<>rowarray),ROWS(A$1:A1)),"")

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	97680	97702	97680
2	97681	3	97689
3	97682		97697
4	97683		97699
5	97684
6	97685
7	97686
8	97687
9	97688
10	97690
11	97691
12	97692
13	97693
14	97694
15	97695
16	97696
17	97698
18	97700
19	97701
20	97702
21
Main

Cell Formulas
Range	Formula
B1	B1	=MAX(A1:A99100)
B2	B2	=NETWORKDAYS.INTL(C1,B1,"0000000",A1:A99100)
C1	C1	=MIN(A1:A99100)
C2:C20	C2	=IF(ROWS(C$2:C2)>$B$2,"",WORKDAY.INTL(C1,1,"0000000",$A$2:$A$99063))

 

[drockdamian]

Trying a couple of things in excel, I'm wondering if it might be too much to process with the original formula and the full range of numbers, I've been noticing some delay when I tried anything using countifs.

This works for me in office 365 using methods that are compatible with older versions of excel. Hopefully it will work for you as well.

Firstly, you need to create a named range (it can be done without but this improves performance with a larger range of numbers).

Go to the Formulas tab on the excel ribbon, then click Name Manager, followed by New and enter the following into the boxes.
You will need to change the sheet name to match yours, and the references to the data column if different.

Name: rowarray
Scope: Workbook
Refers to: =ROW(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MAX(Sheet1!$A:$A)))

Then enter this formula into your sheet and array confirm it with Ctrl Shift Enter before filling down as needed.

=IFERROR(AGGREGATE(15,6,rowarray/(LOOKUP(N(IF({1},rowarray)),A:A)<>rowarray),ROWS(A$1:A1)),"")

Thank you so much! This worked perfectly.

 

[drockdamian]

How about

Fluff.xlsm
	A	B	C
1	97680	97702	97680
2	97681	3	97689
3	97682		97697
4	97683		97699
5	97684
6	97685
7	97686
8	97687
9	97688
10	97690
11	97691
12	97692
13	97693
14	97694
15	97695
16	97696
17	97698
18	97700
19	97701
20	97702
21
Main

Cell Formulas
Range	Formula
B1	B1	=MAX(A1:A99100)
B2	B2	=NETWORKDAYS.INTL(C1,B1,"0000000",A1:A99100)
C1	C1	=MIN(A1:A99100)
C2:C20	C2	=IF(ROWS(C$2:C2)>$B$2,"",WORKDAY.INTL(C1,1,"0000000",$A$2:$A$99063))

Thank you so much! This worked too.