How to count the number of gaps in a sequence?

Question

Hello everyone,

How would I go about counting the number of gaps in a dated sequence of invoices that reset at 500 every day?  For example

Column A   Column B

Date:             Invoice #

1/1/2018       500

1/1/2018       501

1/1/2018       502

1/1/2018       504

1/1/2018       507

1/1/2018       508

1/1/2018       509

1/1/2018       510

1/2/2018       500

1/2/2018       501

1/2/2018       503

1/2/2018       504

1/2/2018       506

Column A continues day after day with the invoice number count resetting to 500 every day.  I need to know how many invoice gaps there are everyday.  Not what numbers are missing, just how many.   For example on 1/1/2018 there are approximately 3 missing invoices and 2 missing invoices on 1/2/2018.  Thank you!

Answer 1

Enter a date such as 1/1/2018 in, say, E2.

In the next cell (F2), enter the following array formula, confirmed with Ctrl+Shift+Enter:

=MAX(IF($A$2:$A$100=E2,$B$2:$B$100))-MIN(IF($A$2:$A$100=E2,$B$2:$B$100))-COUNTIF($A$2:$A$100,E2)+1

Answer 2

Hi Hans,

Thank you for responding.  I attempted to use your formula, but I'm afraid it isn't working correctly.

I realized on some lines, I have the same invoice number repeated, not sure if that makes a difference. 

For example on 1/1/2018, I have 90 lines of data.  The invoices start at 500 and end at 661.  I manually counted 75 gaps in invoices for the day, but your formula counted 72.

I have a few instances where the same invoice is repeated: invoice 507 and 611 are repeated twice.  Also, there are some instances where 6 or 7 invoices in a row are missing.  I'm not sure if those particular situations throw off your formula, because I'm not familiar with what exactly your formula is doing.

Any ideas?

Answer 3

Hi,

To create a list of unique dates based on your table, you can use following ARRAY formula.

Date:	Invoice			Date
01/01/2018	500			01/01/2018	3
01/01/2018	501			01/02/2018	2
01/01/2018	502
01/01/2018	504
01/01/2018	507
01/01/2018	508
01/01/2018	509
01/01/2018	510
01/02/2018	500
01/02/2018	501
01/02/2018	503
01/02/2018	504
01/02/2018	506

In E2: =IFERROR(INDEX(IF($A$2:$A$1000<>"",$A$2:$A$1000,""),MATCH(0,IF($A$2:$A$1000<>"",COUNTIF($E$1:E1,$A$2:$A$1000)),0)),"")

To enter with CTRL+SHIFT+ENTER as array formula.

In F2, a modified version of Hans' array formula:

=IF(E2<>"",MAX(IF($A$2:$A$1000=E2,$B$2:$B$1000))-MIN(IF($A$2:$A$1000=E2,$B$2:$B$1000))-COUNTIF($A$2:$A$1000,E2)+1,"")

Answer 4

Hi JP,

Thanks for trying to help me get this right.  I think I am missing something.  The formulas you have given above are still coming up short.  Below is all of my data for 1/1/2018 with a hand check of missing invoices in column C.  I counted it twice, so it's either right... or I should go back to 1st grade.

In E2: I pasted your formula above, selected the whole formula and pressed CTRL+SHIFT+ENTER.  It then showed 01/01/2018.

In F2: I pasted Hans' modified formula, selected the whole formula and pressed CTRL+SHIFT+ENTER.  It then showed 72.  It showed 72 before and after I pressed CTRL+****+ENTER.

Are the formulas somehow not capturing all of the different scenarios presented below, which is why it is coming up short?  These formulas are truly beyond my expertise, but it is the only thing I can think of.

DATA1	CHKNUM	# of Gaps
01/01/2018	500
01/01/2018	501
01/01/2018	502
01/01/2018	504	1
01/01/2018	506	1
01/01/2018	507
01/01/2018	507
01/01/2018	508
01/01/2018	510	1
01/01/2018	512	1
01/01/2018	513
01/01/2018	514
01/01/2018	516	1
01/01/2018	517
01/01/2018	521	3
01/01/2018	522
01/01/2018	523
01/01/2018	524
01/01/2018	525
01/01/2018	525
01/01/2018	526
01/01/2018	527
01/01/2018	530	2
01/01/2018	535	4
01/01/2018	536
01/01/2018	540	3
01/01/2018	545	4
01/01/2018	546
01/01/2018	548	1
01/01/2018	549
01/01/2018	550
01/01/2018	551
01/01/2018	552
01/01/2018	553
01/01/2018	559	5
01/01/2018	561	1
01/01/2018	562
01/01/2018	565	2
01/01/2018	567	1
01/01/2018	570	2
01/01/2018	572	1
01/01/2018	576	3
01/01/2018	577
01/01/2018	578
01/01/2018	579
01/01/2018	580
01/01/2018	581
01/01/2018	582
01/01/2018	583
01/01/2018	585	1
01/01/2018	587	1
01/01/2018	588
01/01/2018	590	1
01/01/2018	591
01/01/2018	592
01/01/2018	594	1
01/01/2018	595
01/01/2018	596
01/01/2018	599	2
01/01/2018	600
01/01/2018	601
01/01/2018	602
01/01/2018	604	1
01/01/2018	608	3
01/01/2018	609
01/01/2018	610
01/01/2018	611
01/01/2018	611
01/01/2018	612
01/01/2018	619	6
01/01/2018	620
01/01/2018	621
01/01/2018	622
01/01/2018	623
01/01/2018	625	1
01/01/2018	626
01/01/2018	627
01/01/2018	628
01/01/2018	630	1
01/01/2018	632	1
01/01/2018	634	1
01/01/2018	635
01/01/2018	636
01/01/2018	641	4
01/01/2018	645	3
01/01/2018	648	2
01/01/2018	649
01/01/2018	652	2
01/01/2018	658	5
01/01/2018	661	2
		75 Total

Answer 5

The two formula methods seem to come adrift when there are duplicate numbers

(Shame you did not mention duplicates at the outset)

This VBA function seems to work

best wishes

Function missing(mydate)

   Application.Volatile

   lastrow = Cells(Rows.Count, "A").End(xlUp).Row

   olddata = 0

   mymiss = 0

   For k = 2 To lastrow

     If Cells(k, "A") > mydate Then Exit For

     If Cells(k, "A") = mydate Then

       mycount = mycount + 1

       newdata = Cells(k, "B")

       If olddata = 0 Then

         olddata = newdata

       ElseIf newdata > olddata + 1 Then

         mymiss = mymiss + (newdata - olddata) - 1

       End If

       olddata = newdata

    End If

    Next k

    missing = mymiss 

End Function