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
1				Missing Dates	Missing Numbers
2				Nov 26/17, Dec 01/17, Dec 05/17, Dec 10/17	43065, 43070, 43074, 43079
3	Date	Number		11/26/2017	43065
4	11/24/2017	43063		12/1/2017	43070
5	11/25/2017	43064		12/5/2017	43074
6	11/27/2017	43066		12/10/2017	43079
7	11/28/2017	43067		#NUM!	#NUM!
8	11/29/2017	43068		#NUM!	#NUM!
9	11/30/2017	43069		copy on downwards	copy on downwards
10	12/2/2017	43071
11	12/3/2017	43072
12	12/4/2017	43073
13	12/6/2017	43075
14	12/7/2017	43076
15	12/8/2017	43077
16	12/9/2017	43078
17	12/11/2017	43080
18	12/12/2017	43081
19	12/13/2017	43082
20	12/14/2017	43083
21	12/15/2017	43084
22	12/16/2017	43085
23	12/17/2017	43086

<tbody>
</tbody>

Sheet44​

Worksheet Formulas Cell Formula E3 =D3 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=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"))} E2 {=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))),""))} D3 {=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))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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