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 below formula as an array formula (Ctrl+Shift+Enter) in cell F2:

=SUM(IF(FREQUENCY(IF(A2:A100=E2,B2:B100),ROW(INDIRECT(500&":"&MAX(IF(A2:A100=E2,B2:B100)))))=0,1))-1

This will give the count of missing numbers for the relevant date (E2).

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Overlapping_Missing_Numbers.xlsx

Regards

Amit Tandon

www.globaliconnect.com

http://twitter.com/AmitTandonExcel

Answer 2

The above formula uses INDIRECT Function. Below option can also be used which avoids using INDIRECT:

=SUM(IF(FREQUENCY(IF(A2:A100=E2,B2:B100),IF((ROW(1:5000)>=500)*(ROW(1:5000)<=MAX(IF(A2:A100=E2,B2:B100))),ROW(1:5000)))=0,1))-1

This formula Presumes MAX number (in column B) is 5000 - if the MAX could be higher then update the 5000 to what could be MAX in this part of formula: "ROW(1:5000)". So if MAX in column B could be 10000, then update to "ROW(1:10000)".

If you use INDIRECT as mentioned in the previous reply, then no updation is required.

Both options are given in the excel file uploaded earlier.

Regards

Amit Tandon

www.globaliconnect.com

http://twitter.com/AmitTandonExcel

Answer 3

Hi Bernard and Amit,

Amit - Your formulas both worked perfectly.  However, I have 5 years of data to go through using these formulas.  When I attempted to duplicate the formulas for 5 years of data it completely froze out Excel.  I think the constant repeating formula (every day over a 5 year period) is so CPU intensive due to the amount of calculations that are being made, that it will take forever to complete this analysis.  Right now, I'm going to use it as a last resort and duplicate the formula in small increments if I have to.

Bernard - I noticed an error in your formula when I tested it.  Since the invoices reset each day, your formula is starting its calculations with the very first invoice listed under that day.  It is not looking for 500 and then counting upwards.  So in this case, if invoices 500-502 are missing, your formula is not counting those as gaps.  Your formula is starting with invoice 503 and counting the gaps that occur after that.  Is there a way you can fix it?  This vba stuff is way beyond my comprehension.

I really appreciate the help from both of you.

Answer 4

The first 2 options (cell F2 & F5) have already been mentioned earlier. There is also a 3rd option: enter below formula as an array formula (Ctrl+Shift+Enter) in cell F9:

=MAX(IF(A2:A100=E2,B2:B100))-500-SUM(IF(B2:B100<>"",IF(A2:A100=E2,1/(COUNTIFS(A2:A100,E2,B2:B100,B2:B100))),0))+1

The first option uses INDIRECT, which is a volatile function and it increases processing time & slows down workbook if used in a large number of cells.

In respect of the other formulas also, because of the large number of cells and the formulas being somewhat complex, you are experiencing the extra processing time ... you may try the latest (3rd option) in case that may work faster.

Note: The formulas have also now been illustrated in the same excel file (whose link was given earlier).

Regards

Amit Tandon

www.globaliconnect.com

http://twitter.com/AmitTandonExcel

Answer 5

Hi,

Another approach with VBA:

Function missing(mydate As Range, InputRange As Range)

   Dim rngFirst         As Range, rngLast As Range

   Dim varData          As Variant

   Dim dicInvoices      As Object

   Dim lngCount         As Long

   Set dicInvoices = CreateObject("Scripting.Dictionary")

   Set rngFirst = InputRange.Find(What:=mydate.Text, After:=InputRange.Cells(1, 1), LookIn:=xlValues, _

       LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

   Set rngLast = InputRange.Find(What:=mydate.Text, LookIn:=xlValues, _

      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

   varData = Range(InputRange.Cells(rngFirst.Row, InputRange.Columns(1).Column), _

      InputRange.Cells(rngLast.Row, InputRange.Columns.Count))

   For lngCount = 1 To UBound(varData)

      If Not dicInvoices.Exists(CStr(varData(lngCount, 2))) Then

         dicInvoices.Add Key:=CStr(varData(lngCount, 2)), Item:=1

      End If

   Next lngCount

   missing = rngLast.Offset(0, 1) - rngFirst.Offset(0, 1) - dicInvoices.Count + 1 + (rngFirst.Offset(0, 1) - 500)

End Function