Home » Count Unique Odd Digit Numbers

Count Unique Odd Digit Numbers

Yesterday, I ended up posting a complex challenge. This deterred many persons from answering it. So, let’s reduce the complexity back to normal. Find the count of those numbers between From and To which 1. Are odd 2. Have unique digits. 1076 has unique digits whereas 1071 is not having unique digits as 1 gets repeated. Hence for range 150 to 170, odd numbers are 151, 153, 155, 157, 159, 161, 163, 165, 167, 169. 151, 155, 161 are not having unique digits. Hence answer would be 7.

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

Solving the challenge of Count Unique Odd Digit Numbers with Power Query

Power Query solution 1 for Count Unique Odd Digit Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Table.AddColumn(
    Source, 
    "Count", 
    each List.Count(
      List.Select(
        {[From] .. [To]}, 
        each List.Distinct(Text.ToList(Number.ToText(_)))
          = Text.ToList(Number.ToText(_)) and Number.IsOdd(_)
      )
    )
  )[[Count]]
in
  Custom1
Power Query solution 2 for Count Unique Odd Digit Numbers, proposed by Luan Rodrigues: 
let
  Fonte = Data, 
  a = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [a = {[From] .. [To]}, b = List.Select(a, each Number.IsOdd(_))][b]
  ), 
  b = Table.ExpandListColumn(a, "Personalizar"), 
  c = Table.AddColumn(
    b, 
    "Personalizar.1", 
    each [
      a = Text.ToList(Text.From([Personalizar])), 
      b = List.Count(a), 
      c = List.Count(List.Distinct(a)), 
      d = List.Select({b = c}, each b = c = true){0}?
    ][d]
  ), 
  d = Table.SelectRows(c, each ([Personalizar.1] = true)), 
  Result = Table.Group(d, {"From", "To"}, {{"Contagem", each Table.RowCount(_)}})
in
  Result
Power Query solution 3 for Count Unique Odd Digit Numbers, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ExpectedOutput = Table.AddColumn(
    Source, 
    "Custom", 
    each List.Count(
      List.Select(
        {[From] .. [To]}, 
        each List.IsDistinct(Text.ToList(Text.From(_))) and Number.IsOdd(_)
      )
    )
  )
in
  ExpectedOutput
Power Query solution 4 for Count Unique Odd Digit Numbers, proposed by Matthias Friedmann: 
let
 Source = Excel.CurrentWorkbook(){[Name = "FromTo"]}[Content], 
 #"Added Custom" = Table.AddColumn(
 Source, 
 "Count", 
 each List.Count(
 List.Select(
 {[From] .. [To]}, 
 each [ 
 a = Text.ToList(Text.From(_)),
 b = not Number.IsEven(_) and a = List.Distinct(a)
 ][b]
 )
 )
 )[[Count]]
in
 #"Added Custom"

With Bhavya Gupta's elements:
let
 Source = Excel.CurrentWorkbook(){[Name = "FromTo"]}[Content], 
 #"Added Custom" = Table.AddColumn(
 Source, 
 "Count", 
 each List.Count(
 List.Select(
 {[From] .. [To]}, 
 each Number.IsOdd(_) and List.IsDistinct(Text.ToList(Text.From(_)))
 )
 )
 )[[Count]]
in
 #"Added Custom"
Power Query solution 5 for Count Unique Odd Digit Numbers, proposed by Jan Willem Van Holst: 
let
  FXdigitUnique = (x) =>
    let
      s1 = Text.ToList(Number.ToText(x)), 
      s2 = List.Distinct(s1), 
      s3 = List.Count(s1) = List.Count(s2)
    in
      s3, 
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUTIyABKWSrE6QK6BAVgARBobgYUsjU2AHEMDC5CYiSFYzNjIGKQFBEByppZAI2JjAQ==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [From = _t, To = _t, Count = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"From", Int64.Type}, {"To", Int64.Type}, {"Count", Int64.Type}}
  ), 
  Result = Table.AddColumn(
    #"Changed Type", 
    "Result", 
    each 
      let
        listOfNumbers      = {[From] .. [To]}, 
        listOfOddNumbers   = List.Select(listOfNumbers, Number.IsOdd), 
        listOfUniqueNumber = List.Select(listOfNumbers, FXdigitUnique), 
        intersectedNumbers = List.Intersect({listOfOddNumbers, listOfUniqueNumber})
      in
        List.Count(intersectedNumbers)
  )
in
  Result

Solving the challenge of Count Unique Odd Digit Numbers with Excel

Excel solution 1 for Count Unique Odd Digit Numbers, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:A5,B2:B5,LAMBDA(a,b,COUNT(MAP(SEQUENCE(b-a+1,,a),LAMBDA(s,IF(MOD(s,2),1/(ROWS(UNIQUE(MID(s,SEQUENCE(LEN(s)),1)))=LEN(s))))))))
Excel solution 2 for Count Unique Odd Digit Numbers, proposed by Rick Rothstein: 
=MAP(A2:A5,B2:B5,LAMBDA(a,b,LET(s,SEQUENCE((b-a+1)/2,,a+ISEVEN(a),2),SUM(MAP(s,LAMBDA(x,LET(u,UNIQUE(MID(x,SEQUENCE(LEN(x)),1)),0+(COUNT(-u)=LEN(x)))))))))
Excel solution 3 for Count Unique Odd Digit Numbers, proposed by John V.: 
=MAP(A2:A5,B2:B5,LAMBDA(a,b,SUM(MAP(SEQUENCE(1+b-a,,a),LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),u,UNIQUE(m),(COUNTA(m)=COUNTA(u))*ISODD(x)))))))
Excel solution 4 for Count Unique Odd Digit Numbers, proposed by Zoran Milokanović: 
= LEN(REPLACE(RL.NUM_TEXT, '3', '')) + CASE WHEN CHARINDEX('3', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 AND LEN(RL.NUM_TEXT) = LEN(REPLACE(RL.NUM_TEXT, '4', '')) + CASE WHEN CHARINDEX('4', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 AND LEN(RL.NUM_TEXT) = LEN(REPLACE(RL.NUM_TEXT, '5', '')) + CASE WHEN CHARINDEX('5', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 AND LEN(RL.NUM_TEXT) = LEN(REPLACE(RL.NUM_TEXT, '6', '')) + CASE WHEN CHARINDEX('6', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 AND LEN(RL.NUM_TEXT) = LEN(REPLACE(RL.NUM_TEXT, '7', '')) + CASE WHEN CHARINDEX('7', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 AND LEN(RL.NUM_TEXT) = LEN(REPLACE(RL.NUM_TEXT, '8', '')) + CASE WHEN CHARINDEX('8', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 AND LEN(RL.NUM_TEXT) = LEN(REPLACE(RL.NUM_TEXT, '9', '')) + CASE WHEN CHARINDEX('9', RL.NUM_TEXT) = 0 THEN 0 ELSE 1 END
 THEN 'YES'
 ELSE 'NO'
 END = 'YES'
 THEN 1
 ELSE 0
 END
 ) AS "COUNT"
FROM DATA_PREPARATION DP
JOIN RANGE_LIMIT RL ON RL.NUM BETWEEN DP."FROM" AND DP."TO"
GROUP BY
 DP.ORDINAL_NUMBER
,DP."FROM"
,DP."TO"
ORDER BY
 DP.ORDINAL_NUMBER
OPTION (MAXRECURSION 1000)
Excel solution 5 for Count Unique Odd Digit Numbers, proposed by محمد حلمي: 
=MAP(A2:A5,B2:B5,LAMBDA(a,b,SUM(MAP(
SEQUENCE(b-a+1,,a),LAMBDA(r,
(COUNT(FIND(ROW(1:10)-1,r))=LEN(r))*ISODD(r))))))
Excel solution 6 for Count Unique Odd Digit Numbers, proposed by محمد حلمي: 
=MAP(A2:A5,B2:B5,LAMBDA(a,b,SUM(--(MAP(SEQUENCE(b-a+1,,a),LAMBDA(r, AND(COUNT(SEARCH(ROW(1:10)-1,r))=LEN(r),ISODD(r))))))))
Excel solution 7 for Count Unique Odd Digit Numbers, proposed by محمد حلمي: 
=MAP(
A2:A5,B2:B5,
LAMBDA(a,b,
SUM(--(
MAP(SEQUENCE(b-a+1,,a),LAMBDA(r, AND(
LEN(CONCAT(UNIQUE(MID(r,SEQUENCE(LEN(r)),1))))=LEN(r),
ISODD(r))))))))
Excel solution 8 for Count Unique Odd Digit Numbers, proposed by Kris Jaganah: 
=LET(a,A2:A5,b,B2:B5,c,MAP(a,b,LAMBDA(p,q,SUM(BYROW(SEQUENCE(q-p+1,,p,1), LAMBDA(x,MOD(x,2)*(--(LEN(x)=LEN(TEXTJOIN("",TRUE,UNIQUE(MID(x,SEQUENCE(,5),1),TRUE)))))))))),c)
Excel solution 9 for Count Unique Odd Digit Numbers, proposed by Julian Poeltl: 
=MAP(
    A2:A5,
    B2:B5,
    LAMBDA(
        F,
        T,
        LET(
            S,
            SEQUENCE(
                T-F+1,
                ,
                F
            ),
            SUM(
                --ISODD(
                    S
                )*MAP(
                    S,
                    LAMBDA(
                        A,
                        ROWS(
                            UNIQUE(
                                MID(
                                    A,
                                    SEQUENCE(
                                        LEN(
                                            A
                                        )
                                    ),
                                    1
                                )
                            )
                        )=LEN(
                                            A
                                        )
                    )
                )
            )
        )
    )
)
Excel solution 10 for Count Unique Odd Digit Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
=MAP(
    
     A1:A4,
    
     B1:B4,
    
     LAMBDA(
         a,
          b,
         
          LET(
              
               _s,
               SEQUENCE(
                   b - a,
                    ,
                    a
               ),
              
               _e,
               LAMBDA(
                   x,
                   
                    AND(
                        
                         ISODD(
                             x
                         ),
                        
                         COUNT(
                             FIND(
                                 SEQUENCE(
                                     10,
                                      ,
                                      0
                                 ),
                                  x
                             )
                         ) = LEN(
                             x
                         )
                         
                    )
                    
               ),
              
               _c,
               MAP(
                   _s,
                    _e
               ),
              
               _r,
               SUM(
                   N(
                       _c
                   )
               ),
              
               _r
               
          )
          
     )
    
)
Excel solution 11 for Count Unique Odd Digit Numbers, proposed by Timothée BLIOT: 
=LET(Data,
     A2:B5,
    
Odds,
     LAMBDA(
         a,
         b,
          FILTER(
              SEQUENCE(
                  b-a+1,
                  ,
                  a
              ),
              ISODD(
                  SEQUENCE(
                  b-a+1,
                  ,
                  a
              )
              )
          )
     ),
    
UniqDigits,
     LAMBDA(a,
    --(ROWS(
        MID(
            a,
            SEQUENCE(
                LEN(
                    a
                )
            ),
            1
        )
    )= ROWS(
        UNIQUE(
        MID(
            a,
            SEQUENCE(
                LEN(
                    a
                )
            ),
            1
        )
    )
    ))),
    
MAP(
    SEQUENCE(
        ROWS(
            Data
        )
    ),
     LAMBDA(
         x,
          SUM(
              MAP(
                   Odds(
                       INDEX(
                           Data,
                           x,
                           1
                       ),
                        INDEX(
                            Data,
                            x,
                            2
                        )
                   ),
                   LAMBDA(
                       a,
                        UniqDigits(
                    a
                ) 
                   )
              )
          ) 
     )
) )
Excel solution 12 for Count Unique Odd Digit Numbers, proposed by Stefan Olsson: 
=MAP(A2:A5, B2:B5, LAMBDA(_f, _t, COUNTIFS(MAP(SEQUENCE((_t-_f+1)/2, 1, _f+ISEVEN(_f), 2), LAMBDA(x, (LEN(x)=COUNTA(UNIQUE(SPLIT(REGEXREPLACE(x&"", "(.)", "$1|"), "|"), TRUE))))), TRUE)))
Excel solution 13 for Count Unique Odd Digit Numbers, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=MAP(A2:A5,B2:B5, 
LAMBDA(x,y, 
LET(a,SEQUENCE(y-x+1,,x), 
SUM(--MAP(a,LAMBDA(x,ISODD(x)*(COUNTA(UNIQUE(MID(x,SEQUENCE(LEN(x)),1)))= 
LEN(x))))))))
Excel solution 14 for Count Unique Odd Digit Numbers, proposed by Guillermo Arroyo: 
= BYROW(
 A2:B5,
 LAMBDA(
 y,
 LET(
 _list,
 SEQUENCE(INDEX(y, , 2) - INDEX(y, , 1) + 1, , INDEX(y, , 1), 1),
 _Unique,
 MAP(
 _list,
 LAMBDA(
 x,
 COLUMNS(UNIQUE(MID(x, SEQUENCE(, LEN(x)), 1), 1)) = LEN(x))),
 _ODD,
 ISODD(_list),
 ROWS(FILTER(_list, _Unique * _ODD)))))
Excel solution 15 for Count Unique Odd Digit Numbers, proposed by roberto mensa: 
=SUM(--(LEN(SEQUENCE((B2-A2+1)/2,
    ,
    ODD(
        A2-1
    ),
    2))=MMULT(ISNUMBER(FIND(SEQUENCE(
        ,
        10,
        0,
        
    ),
    SEQUENCE((B2-A2+1)/2,
    ,
    ODD(
        A2-1
    ),
    2)))*1,
    SEQUENCE(
        10,
        ,
        ,
        0
    ))))
Excel solution 16 for Count Unique Odd Digit Numbers, proposed by roberto mensa: 
=SUM(--(LEN(ROW(OFFSET($A$1,A2-1,,(B2-A2+1)/2))*2-ODD(A2-1))=MMULT(ISNUMBER(FIND(COLUMN(A:J)-1,ROW(OFFSET($A$1,A2-1,,(B2-A2+1)/2))*2-ODD(A2-1)))*1,ROW($1:$10)^0)))
Excel solution 17 for Count Unique Odd Digit Numbers, proposed by Mohammad Afrooz: 
=SUM(BYROW(SEQUENCE((B38-A38)+1,
    1,
    A38,
    1),
    LAMBDA(b,
    if(ISODD(
        b
    ),
    (LAMBDA(
        a,
         if(
             len(
                 text(
                     a,
                     "#"
                 )
             )=count(
                 unique(
                     SPLIT(
                         REGEXREPLACE(
                             a,
                             "(.)",
                             "$1|" 
                         ),
                         "|",
                         true
                     ),
                     true
                 )
             ),
             true,
             false
         )
    )(text(
        b,
        "#"
    )))*1,
    0))))

Solving the challenge of Count Unique Odd Digit Numbers with Python

Python solution 1 for Count Unique Odd Digit Numbers, proposed by &Igor Perković: 
import pandas as pd
# SOURCE
df = pd.read_excel('C97.xlsx')
# Processing
acc = {}
for e,r in enumerate(df.values.tolist()):
 tmp=[]
 for i in range(r[0], r[1]+1):
 if i%2 != 0:
 sn = str(i)
 if sn not in tmp and len(set(sn))==len(str(i)):
 tmp.append(sn)
 acc[e] = len(tmp)
df['Check'] = [v for k,v in acc.items()]
# Result
print(df.to_string(index=False))

Solving the challenge of Count Unique Odd Digit Numbers with Python in Excel

Python in Excel solution 1 for Count Unique Odd Digit Numbers, proposed by Alejandro Campos: 
results = pd.DataFrame([
 {'From': f, 'To': t, 'Count': sum(n % 2 and len(set(str(n))) == len(str(n)) for n in range(f, t + 1))}
 for f, t in [(1, 20), (100, 200), (934, 1080), (323, 99999)]])

Solving the challenge of Count Unique Odd Digit Numbers with SQL

SQL solution 1 for Count Unique Odd Digit Numbers, proposed by Zoran Milokanović: 
1/2
WITH -- Microsoft SQL Server 2019
DATA
AS
(
 SELECT 1 AS "FROM", 20 AS "TO" UNION ALL
 SELECT 100 AS "FROM", 200 AS "TO" UNION ALL
 SELECT 934 AS "FROM", 1080 AS "TO" UNION ALL
 SELECT 323 AS "FROM", 99999
),
DATA_PREPARATION 
AS
(
 SELECT
 ROW_NUMBER() OVER (ORDER BY ORDERING) AS ORDINAL_NUMBER
 ,T."FROM"
 ,T."TO"
 FROM
 (
 SELECT
 1 AS ORDERING
 ,D."FROM"
 ,D."TO"
 FROM DATA D
 ) T
),
COUNTERS
AS
(
 SELECT 1 AS NUM
 UNION ALL
 SELECT
 C.NUM + 1 AS NUM
 FROM COUNTERS C
 WHERE
 C.NUM < 1000
),
RANGE_LIMIT
AS
(
 SELECT
 ROW_NUMBER() OVER (ORDER BY C1.NUM) AS NUM
 ,CAST(ROW_NUMBER() OVER (ORDER BY C1.NUM) AS VARCHAR) AS NUM_TEXT
 FROM COUNTERS C1
 CROSS JOIN COUNTERS C2
)
SELECT
 DP."FROM"
,DP."TO"
,SUM
 (
 CASE
 CASE

Leave a Reply