Extracting missing numbers or dates

[DRSteele]

I watched this video and modified the formulas. This will extract the missing values from between the minimum and maximum in the range (sorted or not) into either a single cell or a range of cells. Function TEXTJOIN requires Excel 2016, I think.

Is there a superior method?

https://www.youtube.com/watch?v=_jel_Otcgds

	A	B	C	D	E
Missing Dates	Missing Numbers
copy on downwards	copy on downwards

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4"]Nov 26/17, Dec 01/17, Dec 05/17, Dec 10/17[/TD]
[TD="bgcolor: #C6E0B4"]43065, 43070, 43074, 43079[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFF2CC"]Date[/TD]
[TD="bgcolor: #FFF2CC"]Number[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/26/2017[/TD]
[TD="align: right"]43065[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]11/24/2017[/TD]
[TD="align: right"]43063[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/1/2017[/TD]
[TD="align: right"]43070[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11/25/2017[/TD]
[TD="align: right"]43064[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/5/2017[/TD]
[TD="align: right"]43074[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]11/27/2017[/TD]
[TD="align: right"]43066[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/10/2017[/TD]
[TD="align: right"]43079[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]11/28/2017[/TD]
[TD="align: right"]43067[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]11/29/2017[/TD]
[TD="align: right"]43068[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]11/30/2017[/TD]
[TD="align: right"]43069[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12/2/2017[/TD]
[TD="align: right"]43071[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]12/3/2017[/TD]
[TD="align: right"]43072[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12/4/2017[/TD]
[TD="align: right"]43073[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/6/2017[/TD]
[TD="align: right"]43075[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]12/7/2017[/TD]
[TD="align: right"]43076[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]12/8/2017[/TD]
[TD="align: right"]43077[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]12/9/2017[/TD]
[TD="align: right"]43078[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]12/11/2017[/TD]
[TD="align: right"]43080[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]12/12/2017[/TD]
[TD="align: right"]43081[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]12/13/2017[/TD]
[TD="align: right"]43082[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]12/14/2017[/TD]
[TD="align: right"]43083[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]12/15/2017[/TD]
[TD="align: right"]43084[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]12/16/2017[/TD]
[TD="align: right"]43085[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]12/17/2017[/TD]
[TD="align: right"]43086[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet44​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]E3[/TH]
[TD="align: left"]=D3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]D2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",1,TEXT(IF(ISNA(MATCH(ROW(INDIRECT(MIN($A$4:$A$23)&":"&MAX($A$4:$A$23))),$A$4:$A$23,0)),ROW(INDIRECT(MIN($A$4:$A$23)&":"&MAX($A$4:$A$23))),""),"MMM dd/YY"))}[/TD]
[/TR]
[TR]
[TH="width: 10"]E2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",1,IF(ISNA(MATCH(ROW(INDIRECT(MIN($A$4:$A$23)&":"&MAX($A$4:$A$23))),$A$4:$A$23,0)),ROW(INDIRECT(MIN($A$4:$A$23)&":"&MAX($A$4:$A$23))),""))}[/TD]
[/TR]
[TR]
[TH="width: 10"]D3[/TH]
[TD="align: left"]{=SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN($A$4:$A$23)&":"&MAX($A$4:$A$23))),$A$4:$A$23,0)),ROW(INDIRECT(MIN($A$4:$A$23)&":"&MAX($A$4:$A$23)))),ROWS($A$4:A4))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

If your open to a VBA method, then try this:-

NB:- Your actual dates assumed to start "A4", Results start "D4".

[COLOR="Navy"]Sub[/COLOR] MG25Nov35
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] MinDt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] MaxDt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A4", Range("A" & Rows.Count).End(xlUp))
MinDt = Application.Min(Rng): MaxDt = Application.Max(Rng)
c = 3
ReDim nray(MinDt To MaxDt) [COLOR="Navy"]As[/COLOR] Boolean
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
  
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR]
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys: nray(CDbl(DateValue(K))) = True: [COLOR="Navy"]Next[/COLOR] K
                [COLOR="Navy"]For[/COLOR] n = MinDt To MaxDt
                    [COLOR="Navy"]If[/COLOR] nray(n) [COLOR="Navy"]Then[/COLOR]
                        Temp = .Item(n)
                        p = 0
                    [COLOR="Navy"]End[/COLOR] If
                        [COLOR="Navy"]If[/COLOR] Not nray(n) [COLOR="Navy"]Then[/COLOR]
                            c = c + 1
                            p = p + 1
                            Cells(c, "D") = CDate(n)
                            Cells(c, "E") = Temp + p
                        [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[DRSteele]

Thanks tons, Mick. Very helpful.

 

[MickG]

You're welcome