Home » Identify Missing Number Ranges

Identify Missing Number Ranges

Find the missing numbers in the grid given between the smallest and the largest numbers. In answer, consecutive missing numbers need to be shown as start-end numbers.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 603
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Identify Missing Number Ranges with Power Query

Power Query solution 1 for Identify Missing Number Ranges, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = List.Combine(Table.ToRows(A)), 
  C = {List.Min(B) .. List.Max(B)}, 
  D = List.Difference(C, B), 
  E = List.Transform(List.Positions(D), each Number.From((try D{_} - D{_ - 1} otherwise 0) <> 1)), 
  F = List.Skip(List.Accumulate(E, {0}, (x, y) => x & {List.Last(x) + y})), 
  G = Table.FromColumns({D, F}, {"Col1", "Col2"}), 
  H = Table.Group(
    G, 
    {"Col2"}, 
    {
      "Answer Expected", 
      each 
        let
          a = Text.From(List.Max([Col1]))
        in
          if Table.RowCount(_) = 1 then a else Text.From(List.Min([Col1])) & "-" & a
    }
  )[[Answer Expected]]
in
  H
Power Query solution 2 for Identify Missing Number Ranges, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content] meta [Table = "A2:C5", Header = false], 
  Values = List.Combine(Table.ToColumns(Source)), 
  Sort = List.Sort(Values), 
  Zip = List.Zip({List.RemoveLastN(Sort, 1), List.Skip(Sort)}), 
  Generate = List.Transform(
    Zip, 
    each [
      N1 = _{0}, 
      N2 = _{1}, 
      D = N2 - N1, 
      N3 = N1 + 1, 
      N4 = N1 + D - 1, 
      R = 
        if N3 = N4 then
          Text.From(N3)
        else if N3 < N4 then
          Text.From(N3) & "-" & Text.From(N4)
        else
          null
    ][R]
  ), 
  Return = List.RemoveNulls(Generate)
in
  Return
Power Query solution 3 for Identify Missing Number Ranges, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  List = List.Sort(List.Combine(Table.ToColumns(Source))), 
  List2 = {List{0} .. List.Max(List)}, 
  A = Table.FromColumns({List.Difference(List2, List)}), 
  Idx = Table.AddIndexColumn(A, "Idx", 0, 1, Int64.Type), 
  Sol = Table.Group(
    Idx, 
    "Idx", 
    {
      {
        "Answer", 
        each 
          let
            a = [Column1], 
            b = if List.Count(a) = 1 then c(a{0}) else c(a{0}) & "-" & c(List.Last(a)), 
            c = Text.From
          in
            b
      }
    }, 
    0, 
    (a, b) => Number.From(Idx[Column1]{b} - Idx[Column1]{b - 1} > 1)
  )
in
  Sol[[Answer]]
Power Query solution 4 for Identify Missing Number Ranges, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Combine = List.Combine(Table.ToRows(Source)), 
  Missing = List.Difference({List.Min(Combine) .. List.Max(Combine)}, Combine), 
  Accum = List.Accumulate(
    List.Skip(Missing), 
    {Missing{0}}, 
    (s, c) => if c = List.Last(s) + 1 then s & {",", c} else s & {":", c}
  ), 
  Split = Text.Split(Text.Combine(List.Transform(Accum, Text.From)), ":"), 
  Transform = List.Transform(
    Split, 
    each 
      if Text.Contains(_, ",") then
        [a = Text.Split(_, ","), b = a{0} & "-" & List.Last(a)][b]
      else
        _
  ), 
  Result = Table.FromColumns({Transform}, {"Answer Expected"})
in
  Result
Power Query solution 5 for Identify Missing Number Ranges, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Nums = List.Combine(Table.ToRows(Source)), 
  MissingNums = List.RemoveItems({List.Min(Nums) .. List.Max(Nums)}, Nums), 
  MinMax = List.Accumulate(
    MissingNums, 
    {}, 
    (a, v) =>
      if a = {} or v > List.Last(List.Last(a)) + 1 then
        a & {{v}}
      else if List.Count(List.Last(a)) = 1 then
        List.RemoveLastN(a) & {List.Last(a) & {v}}
      else
        List.RemoveLastN(a) & {List.RemoveLastN(List.Last(a)) & {v}}
  ), 
  NumRange = List.Transform(
    MinMax, 
    (x) => [T = List.Transform(x, (y) => Text.From(y)), C = Text.Combine(T, "-")][C]
  ), 
  Res = Table.FromList(NumRange, null, {"Answers Expected"})
in
  Res
Power Query solution 6 for Identify Missing Number Ranges, proposed by Mihai Radu O: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = [
    l = List.Sort(List.Combine(Table.ToColumns(Source))), 
    a = {List.Min(l) .. List.Max(l)}, 
    b = List.Difference(a, l), 
    c = List.Generate(
      () => [x = 0, y = 0], 
      each [x] < List.Count(b), 
      each [x = [x] + 1, y = [y] + 1 * Byte.From((b{x} - b{x - 1}) > 1)], 
      each [y]
    ), 
    d = Table.FromColumns({b, c}), 
    e = Table.Group(
      d, 
      "Column2", 
      {
        "Answer expected", 
        each 
          if List.Count([Column1]) = 1 then
            Text.From([Column1]{0})
          else
            Text.From(List.Min([Column1])) & "-" & Text.From(List.Max([Column1]))
      }
    )[Answer expected]
  ][e]
in
  Custom1
Power Query solution 7 for Identify Missing Number Ranges, proposed by Joevan Bedico: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Answer = 
    let
      x = List.Sort(List.Combine(Table.ToRows(Source)))
    in
      Table.FromColumns(
        {
          List.Transform(
            List.Select(List.Zip({x, List.Skip(x)}), each _{1} - _{0} > 1), 
            each Text.Combine(List.Distinct({Text.From(_{0} + 1), Text.From(_{1} - 1)}), "-")
          )
        }, 
        {"Answer"}
      )
in
  Answer

Solving the challenge of Identify Missing Number Ranges with Excel

Excel solution 1 for Identify Missing Number Ranges, proposed by Bo Rydobon 🇹🇭: 
=LET(n,SORT(TOCOL(A2:C5)+1),m,DROP(n,1)-2,TOCOL(IFS(n=m,n,n
Excel solution 2 for Identify Missing Number Ranges, proposed by Rick Rothstein: 
=LET(r,A2:C5,u,UNIQUE(VSTACK(SORT(TOCOL(r)),SEQUENCE(MAX(r)-MIN(r)+1,,MIN(r))),,1),t,TEXTSPLIT(CONCAT(MAP(u,VSTACK(DROP(u,1),TAKE(u,-1)),LAMBDA(x,y,IF(x+1=y,x&"-",x&"|")))),"@","|"),DROP(IFNA(TEXTBEFORE(t,"-")&"-"&TEXTAFTER("-"&t,"-",-1),t),-1))
Excel solution 3 for Identify Missing Number Ranges, proposed by John V.: 
=LET(n,SORT(TOCOL(A2:C5)),s,SEQUENCE(1+MAX(n)-@n,,@n),g,LOOKUP(s,n),DROP(GROUPBY(g,s,LAMBDA(x,@x&REPT(-MAX(x),@xg),,1))
*** Edit: *** With marvelous option by Bo Rydobon 🇹🇭:
✅
=LET(n,SORT(TOCOL(A2:C5)),o,DROP(n+1,-1),m,DROP(n-1,1),FILTER(o&REPT(-m,o
Excel solution 4 for Identify Missing Number Ranges, proposed by Kris Jaganah: 
=LET(a,
    TOCOL(
        A2:C5
    ),
    b,
    MIN(
        a
    ),
    c,
    SEQUENCE(
        MAX(
        a
    )-b+1,
        ,
        b
    ),
    d,
    XLOOKUP(
        c,
        a,
        a,
        ,
        -1
    ),
    DROP(GROUPBY(d,
    c,
    LAMBDA(
        x,
        IF(
            COUNT(
                x
            )>1,
            MIN(
                x
            )&"-"&MAX(
                x
            ),
            MIN(
                x
            )
        )
    ),
    ,
    0,
    ,
    ISERR(d/(c=d))),
    ,
    1))
Excel solution 5 for Identify Missing Number Ranges, proposed by Julian Poeltl: 
=LET(N,TOCOL(A2:C5),M,MIN(N),S,SEQUENCE(MAX(N)-M+1,,M),F,FILTER(S,NOT(ISNUMBER(XMATCH(S,N)))),C,SCAN(0,F,LAMBDA(A,B,IF(B-RIGHT(A,2)=1,A&"-"&B,B))),U,UNIQUE(LEFT(C,2)),A,MAP(U,LAMBDA(A,XLOOKUP(A&"*",C,C,A,2,-1))),R,IF(LEN(A)>2,LEFT(A,2)&"-"&RIGHT(A,2),A),IFERROR(--R,R))
Excel solution 6 for Identify Missing Number Ranges, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _nums, A2:C5,
 _vlvs, TOCOL(_nums),
 _srt, SORT(_vlvs),
 _drop1, DROP(_srt, -1),
 _drop2, DROP(_srt, 1),
 _diff, _drop2 - _drop1,
 _strt, _drop1 + 1,
 _end, _drop1 + _diff - 1,
 _chk, IFS(_strt = _end, _strt & "", _strt < _end, _strt & "-" & _end),
 _rtrn, TOCOL(_chk, 2),
 _rtrn
)
Excel solution 7 for Identify Missing Number Ranges, proposed by Timothée BLIOT: 
=LET(A,SORT(TOCOL(A2:C5)),B,SEQUENCE(MAX(A)-MIN(A)+1,,MIN(A)), C,FILTER(B,MAP(B,LAMBDA(x, SUM(--(x=A))=0))),D,DROP(C,1),E,DROP(C,-1), F,VSTACK(0,--(D-1=E))+2*VSTACK(--(E+1=D),0),G,VSTACK(FILTER(C,F=0), BYROW(HSTACK(FILTER(C,F=2),FILTER(C,F=1)),LAMBDA(x,TEXTJOIN("-",,x)))),SORTBY(G,--LEFT(G,2)))
Excel solution 8 for Identify Missing Number Ranges, proposed by Hussein SATOUR: 
=LET(
    n,
    A2:C5,
    a,
    MIN(
        n
    ),
    b,
    MAX(
        n
    ),
    c,
    SCAN(
        "",
        SEQUENCE(
            b-a+1,
            ,
            a
        ),
        LAMBDA(
            x,
            y,
            IF(
                ISNA(
                    XMATCH(
                        y,
                        TOCOL(
        n
    )
                    )
                ),
                y,
                "/"
            )
        )
    ),
    d,
    TEXTSPLIT(
        TEXTJOIN(
            "-",
            ,
            c
        ),
        ,
        "/",
        1
    ),
    e,
    MID(
        d,
        2,
        2
    )&IF(
        LEN(
            d
        )<5,
        "",
        "-"&LEFT(
            TEXTAFTER(
                d,
                "-",
                -2
            ),
            2
        )
    ),
     FILTER(
         e,
         e<>""
     )
)
Excel solution 9 for Identify Missing Number Ranges, proposed by Sunny Baggu: 
=LET(
    
     _a,
     SORT(
         TOCOL(
             A2:C5
         )
     ),
    
     _b,
     SEQUENCE(
         MAX(
             _a
         ) - MIN(
             _a
         ) + 1,
          ,
          MIN(
             _a
         )
     ),
    
     _c,
     N(
         ISNA(
             XMATCH(
                 _b,
                  _a
             )
         )
     ),
    
     _d,
     VSTACK(
         0,
          _c
     ),
    
     _e,
     NOT(
         DROP(
             _d,
              -1
         )
     ),
    
     _f,
     DROP(
         _d,
          1
     ),
    
     _g,
     _e * _f,
    
     _h,
     SCAN(
         0,
          _g,
          LAMBDA(
              a,
               v,
               a + v
          )
     ) * _f,
    
     _i,
     DROP(
         UNIQUE(
             _h
         ),
          1
     ),
    
     MAP(
         
          _i,
         
          LAMBDA(
              n,
              
               TEXTJOIN(
                   "-",
                    1,
                    UNIQUE(
                        TAKE(
                            FILTER(
                                _b,
                                 _h = n
                            ),
                             {1; -1}
                        )
                    )
               )
               
          )
          
     )
    
)
Excel solution 10 for Identify Missing Number Ranges, proposed by LEONARD OCHEA 🇷🇴: 
=LET(
    n,
    A2:C5,
    m,
    MIN(
        n
    ),
    s,
    SEQUENCE(
        MAX(
        n
    )-m+1,
        ,
        m
    ),
    x,
    ISNA(
        XMATCH(
            s,
            TOCOL(
        n
    )
        )
    ),
    p,
    x*SCAN(
        ,
        x-DROP(
            VSTACK(
                0,
                x
            ),
            -1
        )=1,
        SUM
    ),
    DROP(
        GROUPBY(
            p,
            s,
            LAMBDA(
                x,
                IF(
                    COUNT(
                        x
                    )>1,
                    MIN(
                        x
                    )&-MAX(
                        x
                    ),
                    MIN(
                        x
                    )
                )
            ),
            ,
            0,
            ,
            p>0
        ),
        ,
        1
    )
)
Excel solution 11 for Identify Missing Number Ranges, proposed by Md. Zohurul Islam: 
=LET(
    
    z,
    SORT(
        TOCOL(
            A2:C5
        )
    ),
    
    u,
    SEQUENCE(
        MAX(
            z
        )-MIN(
            z
        )+1,
        ,
        MIN(
            z
        )
    ),
    
    v,
    FILTER(
        u,
        --ISNA(
            XMATCH(
                u,
                z
            )
        )
    ),
    
    w,
    DROP(
        v,
        1
    )-DROP(
        v,
        -1
    ),
    
    p,
    IF(
        w>1,
        0,
        w
    ),
    
    q,
    VSTACK(
        0,
        p
    ),
    
    r,
    IF(
        q>0,
        0,
        1
    ),
    
    s,
    SCAN(
        ,
        r,
        LAMBDA(
            x,
            y,
            x+y
        )
    ),
    
    tt,
    DROP(
        GROUPBY(
            s,
            v,
            ARRAYTOTEXT,
            0,
            0
        ),
        ,
        1
    ),
    
    j,
    MAP(
        tt,
        LAMBDA(
            x,
            LET(
                a,
                --TEXTSPLIT(
                    x,
                    ", "
                ),
                b,
                COUNT(
                    a
                ),
                d,
                IF(
                    b=1,
                    a,
                    MIN(
                    a
                )&"-"&MAX(
                    a
                )
                ),
                d
            )
        )
    ),
    
    j
)
Excel solution 12 for Identify Missing Number Ranges, proposed by ferhat CK: 
=LET(a,TOCOL(A2:C5),mx,MAX(A2:C5),mn,MIN(A2:C5),b,FILTER(SEQUENCE(mx-mn+1,,mn),ISNA(MATCH(SEQUENCE(mx-mn+1,,mn),a,))),c,
SCAN(,N(b-VSTACK(0,DROP(b,-1))<>1),SUM),MAP(UNIQUE(c),LAMBDA(x,LET(q,FILTER(b,c=x),IF(MIN(q)=MAX(q),q,MIN(q)&"-"&MAX(q))))))
Excel solution 13 for Identify Missing Number Ranges, proposed by Meganathan Elumalai: 
=LET(z,A2:C5,c,TOCOL(z),s,SEQUENCE(MAX(z)-MIN(z)+1,,MIN(z)),x,FILTER(s,ISNA(XMATCH(s,c))),TEXTSPLIT(MID(CONCAT(CHOOSE(-MMULT(--ISNA(XMATCH(x+{-1,1},x)),{3;1})/2+3,", "&x,"-"&x,"")),3,99),,", "))
Excel solution 14 for Identify Missing Number Ranges, proposed by Nicolas Micot: 
=LET(_min;MIN(A2:C5);
_max;MAX(A2:C5);
_numbers;SEQUENCE(_max-_min-1;;_min+1);
_missingNumbers;FILTRE(_numbers;NB.SI(A2:C5;_numbers)=0);
_indexes;ASSEMB.V(1;SCAN(1;EXCLURE(_missingNumbers;1) = EXCLURE(_missingNumbers;-1)+1;LAMBDA(l_value;l_tableau;SI(l_tablea&u;l_value;l_value+1))));
MAP(UNIQUE(_indexes);LAMBDA(l_index;LET(_values;FILTRE(_missingNumbers;_indexes=l_index);MIN(_values) & SI(LIGNES(_values)=1; ""; "-" & MAX(_values))))))
Excel solution 15 for Identify Missing Number Ranges, proposed by Britt Deaton, FSA: 
=LET(
    
    Numbers,
    SORT(
        TOCOL(
            A2:C5
        )
    ),
    
    FullNumbers,
    SEQUENCE(
        MAX(
            Numbers
        )-MIN(
            Numbers
        )+1,
        ,
        MIN(
            Numbers
        )
    ),
    
    MissingNumbers,
    UNIQUE(
        VSTACK(
            Numbers,
            FullNumbers
        ),
        ,
        TRUE
    ),
    
    PriorNumber,
    VSTACK(
        {0},
        MissingNumbers
    ),
    
    IsSequentialNumber,
    MissingNumbers=PriorNumber+1,
    
    RangeNum,
    SCAN(
        0,
        IsSequentialNumber,
        LAMBDA(
            a,
            b,
            a+IF(
                b,
                0,
                1
            )
        )
    ),
    
    Ranges,
    SEQUENCE(
        ,
        MAX(
            IFERROR(
                RangeNum,
                0
            )
        )
    ),
    
    RangeResults,
    IF(
        RangeNum=Ranges,
        MissingNumbers,
        NA()
    ),
    
    Rangelist_Min,
    BYCOL(
        RangeResults,
        LAMBDA(
            a,
            MIN(
                IFERROR(
                    a,
                    999
                )
            )
        )
    ),
    
    Rangelist_Max,
    BYCOL(
        RangeResults,
        LAMBDA(
            a,
            MAX(
                IFERROR(
                    a,
                    0
                )
            )
        )
    ),
    
    RangeList,
    IF(
        Rangelist_Min=Rangelist_Max,
        Rangelist_Min,
        Rangelist_Min&"-"&Rangelist_Max
    ),
    
    TRANSPOSE(
        RangeList
    )
)

Solving the challenge of Identify Missing Number Ranges with Python

Python solution 1 for Identify Missing Number Ranges, proposed by Konrad Gryczan, PhD: 
import pandas as pd
path = "603 Missing Numbers.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=4, header=None)
test = pd.read_excel(path, usecols="E", nrows=6).astype("str")
V1 = input.values.flatten()
missing_nums = sorted(set(range(min(V1), max(V1) + 1)) - set(V1))
df_missing = pd.DataFrame(missing_nums, columns=["Missing Numbers"])
df_missing['index'] = (df_missing.diff() != 1).cumsum()
df_missing = df_missing.groupby('index')['Missing Numbers'].apply(
 lambda group: str(group.iloc[0]) if len(group) == 1 else f"{group.iloc[0]}-{group.iloc[-1]}"
).reset_index(drop=True)
print(df_missing.equals(test['Answer Expected'])) # True

Solving the challenge of Identify Missing Number Ranges with Python in Excel

Python in Excel solution 1 for Identify Missing Number Ranges, proposed by Alejandro Campos: 
def find_missing_numbers(numbers):
 numbers.sort()
 missing = [num for num in range(numbers[0], numbers[-1] + 1) if
 num not in numbers]
 grouped = []
 start = end = missing[0]
 for num in missing[1:]:
 if num == end + 1:
 end = num
 else:
 grouped.append((start, end))
 start = end = num
 grouped.append((start, end))
 return grouped
numbers = xl("A2:C5").values.flatten().tolist()
missing_numbers = find_missing_numbers(numbers)
missing_numbers_str = [f"{start}-{end}" if start != end else
 f"{start}" for start, end in missing_numbers]
df = pd.DataFrame(missing_numbers_str, columns=['Missing Numbers'])
df

Solving the challenge of Identify Missing Number Ranges with R

R solution 1 for Identify Missing Number Ranges, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "Excel/603 Missing Numbers.xlsx"
input = read_excel(path, range = "A2:C5", col_names = F) %>% as.matrix()
test = read_excel(path, range = "E1:E6")
V1 = input %>%
 as.vector() 
V2 = min(V1):max(V1)
result = data.frame(nums = setdiff(V2, V1)) %>%
 mutate(group = cumsum(c(1, diff(nums) != 1))) %>%
 summarise(nums = ifelse(n() > 1, 
 paste0(min(nums), "-", max(nums)), 
 as.character(nums)), .by = group) 
all.equal(result$nums, test$`Answer Expected`)
#> [1] TRUE

&&

Leave a Reply