Home » Common Digits in Row

Common Digits in Row

List the common digits between all the numbers in a row. Ex. 809, 108, 128908 = 0, 8 are the common digits between all 3 numbers Sort the answer digits.

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

Solving the challenge of Common Digits in Row with Power Query

Power Query solution 1 for Common Digits in Row, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Res = List.Transform(
    Table.ToColumns(Source){0}, 
    each Text.Combine(
      List.Sort(List.Intersect(List.Transform(Text.Split(_, ","), Text.ToList))), 
      ", "
    )
  )
in
  Res
Power Query solution 2 for Common Digits in Row, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  AddedAnswerExpected = Table.AddColumn(
    Source, 
    "Answer Expected", 
    each List.Sort(List.Intersect(List.Transform(Text.Split([Numbers], ", "), each Text.ToList(_))))
  ), 
  Solution = Table.TransformColumns(
    AddedAnswerExpected, 
    {"Answer Expected", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
  )[[Answer Expected]]
in
  Solution
Power Query solution 3 for Common Digits in Row, proposed by 🇰🇷 Taeyong Shin: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.AddColumn(
    Source, 
    "Num", 
    each 
      let
        Split = Text.Split([Numbers], ", "), 
        TxtList = List.Transform(
          {"0" .. "9"}, 
          each if List.Count(List.FindText(Split, _)) = List.Count(Split) then _ else null
        )
      in
        Text.Combine(TxtList, ", "), 
    type text
  )
in
  Result
Power Query solution 4 for Common Digits in Row, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Common", 
    each [
      Split  = Text.Split([Numbers], ", "), 
      ToList = List.Transform(Split, Text.ToList), 
      Common = List.Sort(List.Intersect(ToList)), 
      Output = Text.Combine(Common, ", ")
    ][Output]
  )
in
  Return
Power Query solution 5 for Common Digits in Row, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Custom", 
    each Text.Combine(
      List.Sort(List.Intersect(List.Transform(Text.Split([Numbers], ", "), Text.ToList))), 
      ", "
    )
  )
in
  Sol
Power Query solution 6 for Common Digits in Row, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  res = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      a = Text.Split([Numbers], ", "), 
      b = List.Transform(a, Text.ToList), 
      c = Text.Combine(List.Sort(List.Intersect(b)), ", ")
    ][c]
  )
in
  res
Power Query solution 7 for Common Digits in Row, proposed by Jan Willem Van Holst: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "LZDLEcQwDEJb8eTsg/4StWTSfxuLkz15AgE/fN/XqPlebrGXDa5n31dEqfLTDXKsltyry7Neu6roluYEQ3Cv0C8HT1DSQJbtFZOFeC2EijcltPgwHt4mvLglzFz7Kxjzpmg2ZXkKxIoEiiHIwJH5/sgoDqDIUM9U//LFqiLBBAcpPYPzTMJwJnnphXkk9N8zCGIVl86crQjwEZ4f", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Numbers = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        split     = Text.Split([Numbers], ", "), 
        transform = List.Transform(split, each Text.ToList(_)), 
        intersect = List.Intersect(transform), 
        sort      = List.Sort(intersect)
      in
        Text.Combine(sort, ", ")
  )
in
  #"Added Custom"
Power Query solution 8 for Common Digits in Row, proposed by Udit Chatterjee: 
let
  fxCommonDigits = (numString as text) =>
    let
      numList                  = Text.Split(numString, ","), 
      digitNestedList          = List.Transform(numList, each Text.ToList(_)), 
      intersectIndividualLists = List.Sort(List.Intersect(digitNestedList)), 
      stringFromList           = Text.Combine(intersectIndividualLists, ", ")
    in
      stringFromList, 
  Source = xlProblem151, 
  addFunctionCol = Table.AddColumn(
    Source, 
    "Common Digits", 
    each fxCommonDigits([Numbers]), 
    type text
  ), 
  keepRequiredCol = Table.SelectColumns(addFunctionCol, {"Common Digits"})
in
  keepRequiredCol

Solving the challenge of Common Digits in Row with Excel

Excel solution 1 for Common Digits in Row, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A10,LAMBDA(a,TEXTJOIN(", ",,MAP(SEQUENCE(10)-1,LAMBDA(n,IF(OR(ISERR(FIND(n,TEXTSPLIT(a,",")))),,n))))))
Excel solution 2 for Common Digits in Row, proposed by Rick Rothstein: 
=MAP(A2:A10,LAMBDA(a,TEXTJOIN(", ",,IFERROR(SEQUENCE(10,,0)*SUBSTITUTE(COUNTIF(a,MAP(SEQUENCE(10,,0),LAMBDA(x,"*"&TEXTJOIN("*,*",,x*SEQUENCE(LEN(a)-LEN(SUBSTITUTE(a,",",""))+1,,,0))&"*"))),0,""),""))))
Excel solution 3 for Common Digits in Row, proposed by Rick Rothstein: 
=MAP(A2:A10,LAMBDA(a,LET(n,TEXTSPLIT(a,", "),s,SEQUENCE(10,,0),TEXTJOIN(", ",,FILTER(s,BYROW(FIND(s,n),LAMBDA(r,ISNUMBER(SUM(r)))),"")))))
Excel solution 4 for Common Digits in Row, proposed by John V.: 
=MAP(A2:A10,LAMBDA(x,LET(n,ROW(1:10)-1,TEXTJOIN(", ",,REPT(n,BYROW(FIND(n,TEXTSPLIT(x,", ")),LAMBDA(r,1-OR(ISERR(r)))))))))
Excel solution 5 for Common Digits in Row, proposed by 🇰🇷 Taeyong Shin: 
=MAP(
    A2:A10,
    LAMBDA(
        x,
        LET(
            n,
            SEQUENCE(
                10,
                ,
                0
            ),
            TEXTJOIN(
                ",",
                ,
                REPT(
                    n,
                    BYROW(
                        REGEXTEST(
                            TEXTSPLIT(
                                x,
                                ", "
                            ),
                            n
                        ),
                        AND
                    )
                )
            )
        )
    )
)
Excel solution 6 for Common Digits in Row, proposed by 🇰🇷 Taeyong Shin: 
=LET(n, SEQUENCE(10) - 1, MAP(A2:A10, LAMBDA(m, TEXTJOIN(", ", , REPT(n, BYROW(FIND(n, TEXTSPLIT(m, ", ")), LAMBDA(r, AND(ISNUMBER(r)))))))) )
Excel solution 7 for Common Digits in Row, proposed by Kris Jaganah: 
=MAP(A2:A10,LAMBDA(v,LET(a,TEXTSPLIT(v,,", "),b,IFNA(DROP(REDUCE("",a,LAMBDA(p,q,(HSTACK(p,--UNIQUE(MID(q,SEQUENCE(LEN(q)),1)))))),,1),""),c,SEQUENCE(10,,0),d,BYROW(DROP(REDUCE("",b,LAMBDA(x,y,HSTACK(x,IFNA(XMATCH(c,y,0),"")))),,1),LAMBDA(z,SUM(z))),TEXTJOIN(", ",1,FILTER(c,d=ROWS(a),"")))))
Excel solution 8 for Common Digits in Row, proposed by Julian Poeltl: 
=MAP(A2:A10,LAMBDA(N,LET(S,SEQUENCE(10,,0),SP,TEXTSPLIT(N,", "),IFERROR(TEXTJOIN(", ",,FILTER(S,BYROW(LEN(SP)<>LEN(SUBSTITUTE(SP,S,"")),LAMBDA(A,SUM(--A)))=COLUMNS(SP))),""))))
Excel solution 9 for Common Digits in Row, proposed by Aditya Kumar Darak 🇮🇳: 
=MAP(
 A2:A10,
 LAMBDA(a,
 LET(
 s, TEXTSPLIT(a, ", "),
 sq, SEQUENCE(10, , 0),
 cl, COLUMNS(s),
 c1, --ISNUMBER(SEARCH(sq, s)),
 c2, MMULT(c1, SEQUENCE(cl, , 1, 0)),
 f, FILTER(sq, c2 = cl, ""),
 r, ARRAYTOTEXT(f),
 r
 )
 )
)
Excel solution 10 for Common Digits in Row, proposed by Timothée BLIOT: 
=LET(A, TEXTSPLIT(TEXTJOIN("/",,A2:A10),", ","/",,,"#"),
B, IFERROR(MAP(A, LAMBDA(a, TEXTJOIN(":",,BYROW(SEQUENCE(10,,0), LAMBDA(x, IF(SUM(SUMPRODUCT(1*(x=1*(MID(a,SEQUENCE(LEN(a)),1)))))>=1,1,0) ))))),""),
C, BYROW(B, LAMBDA(x, TEXTJOIN(":",, BYCOL(TEXTSPLIT(TEXTJOIN("/",,x),":","/"), LAMBDA(a, --(SUM(1*a)=ROWS(TEXTSPLIT(TEXTJOIN("/",,x),":","/"))) )) ) )),
BYROW(1*TEXTSPLIT(TEXTJOIN("/",,C),":","/",,,""), LAMBDA(x, ARRAYTOTEXT( FILTER(SEQUENCE(,10,0),x,"") ) )) )
Excel solution 11 for Common Digits in Row, proposed by Hussein SATOUR: 
=IFERROR(MAP(A2:A10, LAMBDA(n, LET(a, MAP(TEXTSPLIT(n, ", "), LAMBDA(x, CONCAT(UNIQUE(MID(x, SEQUENCE(LEN(x)), 1))))),b, CONCAT(a), c, UNIQUE(MID(b, SEQUENCE(LEN(b)), 1)),d, MAP(c, LAMBDA(y, LEN(b) - LEN(SUBSTITUTE(b,y,"")))), ARRAYTOTEXT(SORT(FILTER(c, d = COUNTA(a))))))), "")
Excel solution 12 for Common Digits in Row, proposed by Oscar Mendez Roca Farell: 
=MAP(A2:A10; LAMBDA(i;
 LET(_c; SUBSTITUTE(i;", ";"");_n; SEQUENCE(10)-1;
 _m;ROUNDUP((LEN(i)-LEN(_c)+1)/2;);
 _a; IFERROR(SEARCH("*|*";
 SUBSTITUTE(TEXTSPLIT(i;", ");_n;"|")););
 _r; FILTER(_n; MMULT(_a;SEQUENCE(_m)^0)=_m;"");
TEXTJOIN(", ";;_r))))
Excel solution 13 for Common Digits in Row, proposed by Duy Tùng: 
=MAP(A2:A10,LAMBDA(x,LET(a,ROW(1:10)-1,TEXTJOIN(", ",,IF(BYROW(ISNUMBER(FIND(a,TEXTSPLIT(x,", "))),AND),a,"")))))
Excel solution 14 for Common Digits in Row, proposed by Sunny Baggu: 
=LET(_tbl,DROP(REDUCE("",A2:A10,LAMBDA(a,v,VSTACK(a,TEXTSPLIT(v,", ")))),1),
MAKEARRAY(ROWS(_tbl),1,LAMBDA(r,c,
IFERROR(
 ARRAYTOTEXT(
 FILTER(SEQUENCE(10,,0),
 BYROW(ISNUMBER(--IFNA(SEARCH(SEQUENCE(10,,0),INDEX(_tbl,r,SEQUENCE(,COLUMNS(_tbl)))),TRUE)),LAMBDA(a,AND(a))))
 ),
 "") )))
Excel solution 15 for Common Digits in Row, proposed by Md. Zohurul Islam: 
=MAP(A2:A10,LAMBDA(x,
LET(
a,TEXTSPLIT(x,,", "),
b,COUNTA(a),
c,DROP(REDUCE("",a,LAMBDA(q,p,VSTACK(q,UNIQUE(ABS(MID(p,SEQUENCE(LEN(p)),1)))))),1),
d,SORT(UNIQUE(c)),
e,MAP(d,LAMBDA(y,SUM(ABS(c=y)))),
f,IFERROR(ARRAYTOTEXT(FILTER(d,e=b)),""),
f)
))
Excel solution 16 for Common Digits in Row, proposed by Stefan Olsson: 
=BYROW(A2:A10, 
LAMBDA(br, 
TEXTJOIN(", ", 1, 
MAP(SEQUENCE(10,1,0), 
LAMBDA(d, 
IF(REGEXMATCH(br, REGEXREPLACE(br, "(d+)(,|$)", ".*?"&d&".*?$2")),d,)
)))))
Excel solution 17 for Common Digits in Row, proposed by Victor Wang: 
= [Numbers]
VAR y =
SUBSTITUTE( x, ", ", "|")
VAR z =
PATHLENGTH( y )
VAR nums =
SELECTCOLUMNS(CALENDAR(0, 9), "int", INT([date]))
VAR tbl =
SELECTCOLUMNS(CALENDAR(1, z ), "num", PATHITEM( y, INT([date])))
VAR tbl2 =
SELECTCOLUMNS(
FILTER(
ADDCOLUMNS(
 nums,
"count",
VAR thenum = [int]
RETURN
COUNTROWS(FILTER( tbl, FIND( thenum, [num], 1, 0) > 0))
),
[count] = z
),
"int", [int]
)
VAR concat =
CONCATENATEX( tbl2, [int], ", ", [int], ASC)
RETURN
 concat
)
Excel solution 18 for Common Digits in Row, proposed by Abdelrahman Omer, MBA, PMP: 
Dear محمد حلمي
Excel solution 19 for Common Digits in Row, proposed by Guillermo Arroyo: 
=MAP(A2:A10,LAMBDA(m,REDUCE("",SEQUENCE(10,,0),LAMBDA(i,j,IF(AND(ISNUMBER(SEARCH(j,TEXTSPLIT(m,", ")))),TEXTJOIN(", ",,i,j),i)))))
Excel solution 20 for Common Digits in Row, proposed by Anup Kumar: 
=BYROW(A2:A10, LAMBDA(a, LET(
Greetings, "Happy March Ending Folks",
spltStr,TEXTSPLIT(a,,", "),
Cnt,ROWS(spltStr),
UnqStr,REDUCE("",spltStr,LAMBDA(i,r,i&CONCAT(UNIQUE(MID(r,SEQUENCE(LEN(r),,1),1))))),
StrArr, MID(UnqStr,SEQUENCE(LEN(UnqStr),,1),1),
CntArr, SCAN(0,StrArr,LAMBDA(k,p,ROWS(FILTER(StrArr,StrArr=p)))),
ans, TEXTJOIN(", ",TRUE,SORT(UNIQUE(FILTER(StrArr,CntArr=Cnt)))),
IFERROR(ans,"No Common digit")
)))
Excel solution 21 for Common Digits in Row, proposed by Mohamed Helmy: 
=MAP(A2:A10,LAMBDA(a,LET(
r,ROW(1:10)-1,
v,TEXTSPLIT(a,","),
ARRAYTOTEXT(FILTER(r,MMULT(-ISERR(FIND(r,v)),
TOCOL(LEN(v)^0))=0,"")))))

Solving the challenge of Common Digits in Row with Python in Excel

Python in Excel solution 1 for Common Digits in Row, proposed by Alejandro Campos: 
Sol #1 only column Common digits
df = xl("A1:A10", headers=True)
df["Numbers"] = df["Numbers"].apply(lambda x: str(x).split(", "))
def common_digits(numbers):
 common = set(numbers[0])
 for num in numbers[1:]:
 common &= set(num)
 return ', '.join(sorted(common))
df["Common Digits"] = df["Numbers"].apply(common_digits)
comm_dig = pd.DataFrame(df['Common Digits'])
comm_dig
Sol #2 with both columns: Numbers & Common Digits
df = xl("A1:A10", headers=True)
nums = df.copy()
df["Numbers"] = df["Numbers"].apply(lambda x: str(x).split&(", "))
def common_digits(numbers):
 common = set(numbers[0])
 for num in numbers[1:]:
 common &= set(num)
 return ','.join(sorted(common))
df["Common Digits"] = df["Numbers"].apply(common_digits)
comm_dig = pd.DataFrame(df['Common Digits'])
result = pd.concat([nums, comm_dig], axis=1)
result

&&

Leave a Reply