Home » List Prime Numbers Only

List Prime Numbers Only

List all prime numbers from A2:A10. Prime numbers are those numbers which can be divided only by 1 and that number itself. Hence, 6 is not a prime number as divisible by 2 and 3. But 11 is a prime number. Prime numbers are positive integers only and exclude 1. Hence, prime numbers start with 2 only. The first few prime numbers are 2,3,5,7,11,13,17,19, 23, 29…..

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

Solving the challenge of List Prime Numbers Only with Power Query

Power Query solution 1 for List Prime Numbers Only, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Calc = Table.AddColumn(
    Source, 
    "Calc", 
    each [
      a = [Number], 
      b = {2 .. Number.Round(Number.Sqrt(Number.Abs(a)))}, 
      c = List.Select(b, (f) => Number.Mod([Number], f) = 0), 
      d = List.Count(c) = 0 and [Number] > 1
    ][d]
  ), 
  Result = Table.SelectRows(Calc, each ([Calc] = true))[[Number]]
in
  Result
Power Query solution 2 for List Prime Numbers Only, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Calc = Table.AddColumn(
    Source, 
    "Calc", 
    each [
      a = {2 .. Number.RoundTowardZero(Number.Sqrt([Number]))}, 
      b = List.Transform(a, (f) => Number.Mod([Number], f) = 0), 
      c = not List.AnyTrue(b) and not List.IsEmpty(b), 
      d = if [Number] = 2 then true else c, 
      e = try d otherwise false
    ][e]
  ), 
  Result = Table.SelectRows(Calc, each ([Calc] = true))[[Number]]
in
  Result
Power Query solution 3 for List Prime Numbers Only, proposed by Brian Julius: 
let
  Source = Table.TransformColumnTypes(#"Prime Raw", {"Number", Int64.Type}), 
  #"Run R script" = R.Execute(
    "library(matlab)#(lf)Answer <- isprime( dataset$Number ) * dataset$Number#(lf)Answerdf <- as.data.frame( Answer )", 
    [dataset = Source]
  ), 
  #"""Answerdf""" = #"Run R script"{[Name = "Answerdf"]}[Value], 
  Clean = Table.SelectRows(#"""Answerdf""", each ([Answer] <> 0))
in
  Clean
Power Query solution 4 for List Prime Numbers Only, proposed by Matthias Friedmann: 
let
  Source = Excel.CurrentWorkbook(){[Name = "PrimeNum"]}[Content], 
  #"Filtered Rows" = Table.SelectRows(
    Source, 
    each 
      let
        Num      = [Number], 
        Divisors = {2 .. Number.RoundDown(Number.Sqrt(Num))}
      in
        Num > 1 and List.IsEmpty(List.Select(Divisors, each Number.Mod(Num, Number.From(_)) = 0))
  )
in
  #"Filtered Rows"
Power Query solution 5 for List Prime Numbers Only, proposed by Venkata Rajesh: 
let
  Source = Data, 
  Prime = Table.SelectRows(
    Table.AddColumn(
      Source, 
      "Check", 
      each 
        let
          _Num = [Number]
        in
          List.Count(
            List.Select(
              {2 .. Number.Round(Number.Sqrt(Number.Abs([Number])))}, 
              each Number.Mod(_Num, Number.From(_)) = 0
            )
          )
    ), 
    each ([Check] = 0 and [Number] > 1)
  )[Number]
in
  Prime
Power Query solution 6 for List Prime Numbers Only, proposed by Hristo Tsenov: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Filter = Table.SelectRows(
    Table.SelectRows(Source, each Number.IsEven([Number]) = false or [Number] = 2), 
    each [Number] >= 2
  ), 
  #"Custom" = Table.AddColumn(
    Filter, 
    "Custom", 
    each List.Numbers(3, Number.Round(Number.Sqrt([Number]), 0, 0), 2)
  ), 
  #"Expand" = Table.ExpandListColumn(#"Custom", "Custom"), 
  #"Custom2" = Table.AddColumn(
    #"Expand", 
    "Custom2", 
    each 
      if [Number] = 2 then
        "Prime"
      else if [Number] = [Custom] then
        "Prime"
      else if (Number.Round([Number] / [Custom]) - [Number] / [Custom]) = 0 then
        "Not Prime"
      else
        "Prime"
  ), 
  #"Group" = Table.Group(
    #"Custom2", 
    {"Number"}, 
    {"Result", each List.Contains([Custom2], "Not Prime"), type logical}
  ), 
  #"Select" = Table.SelectColumns(Table.SelectRows(#"Group", each ([Result] = false)), {"Number"})
in
  #"Select"

Solving the challenge of List Prime Numbers Only with Excel

Excel solution 1 for List Prime Numbers Only, proposed by Julian Poeltl: 
=FILTER(
    A2:A10,
    MAP(
        A2:A10,
        LAMBDA(
            N,
            LET(
                c,
                {2357},
                D,
                N/TOCOL(
                    VSTACK(
                        c,
                        SEQUENCE(
                            N^0,
                            5/6,
                            ,
                            2
                        )*6+{-11}
                    ),
                    3
                ),
                OR(
                    N=c,
                    AND(
                        D-INT(
                            D
                        ),
                        N>1
                    )
                )
            )
        )
    )
)
Excel solution 2 for List Prime Numbers Only, proposed by Alejandro Campos: 
=LET(
 filtro, FILTER(A2:A10, A2:A10 > 1),
 esPrimo, MAP(filtro, LAMBDA(x, SUM(--(FLOOR.MATH(x, ROW(INDIRECT("1:" & INT(SQRT(x))))) = x)) = 1)),
 FILTER(filtro, esPrimo)
)
Excel solution 3 for List Prime Numbers Only, proposed by Aditya Kumar Darak 🇮🇳: 
= FILTER(
    
     A2:A10,
    
     MAP(
         
          A2:A10,
         
          LAMBDA(
              
               a,
              
               OR(
                   AND(
                       MIN(
                           
                            MOD(
                                a,
                                 SEQUENCE(
                                     MAX(
                                         1,
                                          SQRT(
                                              ABS(
                                                  a
                                              )
                                          )
                                     ),
                                      ,
                                      2
                                 )
                            )
                       ) <> 0,
                       
                        a <> 1,
                       
                        a > 0,
                       
                        INT(
                                                  a
                                              ) = a
                   ),
                   
                    a = 2
               )
          )
     )
)
Excel solution 4 for List Prime Numbers Only, proposed by Timothée BLIOT: 
=FILTER(A2:A10,
BYROW(A2:A10,
LAMBDA(r,LET(
integer,IF(r=ROUND(r,0),1,0),
lessTwo,IF(r>=2,1,0),
evenFlag,IF(r=2,1,IF(MOD(r,2)=0,0,1)),
SquareN,IF(integer*lessTwo*evenFlag,ROUNDDOWN(r^(1/2),0),0),
PrimeCheck,
IF(SquareN<=1,
0,
SUM(--(MOD(r,SEQUENCE(SquareN-1,1,2))=0))
),
IF(r>1,IF(PrimeCheck=0,1,0),0)
)))=1)
Excel solution 5 for List Prime Numbers Only, proposed by Bhavya Gupta: 
=FILTER(A2:A10,
MAP(A2:A10,LAMBDA(x,
AND(MOD(x,SEQUENCE(SQRT(x),,2))<>0))))
Excel solution 6 for List Prime Numbers Only, proposed by Bhavya Gupta: 
=FILTER(
    A2:A10,
    
    MAP(
        A2:A10,
        LAMBDA(
            x,
            
            IFS(
                x<2,
                FALSE,
                x=2,
                TRUE,
                ISEVEN(
                    x
                ),
                FALSE,
                INT(
                    x
                )<>x,
                FALSE,
                OR(
                    MOD(
                        x,
                        {2,
                        3,
                        5,
                        7,
                        11}
                    )=0
                ),
                FALSE,
                TRUE,
                AND(
                    MOD(
                        x,
                        SEQUENCE(
                            SQRT(
                    x
                ),
                            ,
                            2
                        )
                    )<>0
                )
            )
        )
    )
)
Excel solution 7 for List Prime Numbers Only, proposed by Charles Roldan: 
=LET(Only, LAMBDA(f, LAMBDA(x, FILTER(x, MAP(x, f)))), Prime, LAMBDA(n, IFERROR(AND(MOD(n, DROP(SEQUENCE(SQRT(n)), 1))), n > 1)), Only(Prime))(A2:A10)
Excel solution 8 for List Prime Numbers Only, proposed by Jardiel Euflázio: 
=FILTER(

A2:A10,
BYROW(A2:A10,LAMBDA(a,SUM(0+(MOD(a,SEQUENCE(a))=0))=2)
)
)
Excel solution 9 for List Prime Numbers Only, proposed by Viswanathan M B: 
=LET(IsPrime, LAMBDA(a, IFS(a<=3, TRUE,
 ISEVEN(a), FALSE,
 ISODD(a), LET(X, a/SEQUENCE((a/2)-2,1,3),                              NOT(OR(X=INT(X))))
 )
 ),
 Rng, A2:A10,
 FILTER(Rng, MAP(Rng, IsPrime))
 )
Excel solution 10 for List Prime Numbers Only, proposed by Viswanathan M B: 
=LET(IsPrime, LAMBDA(a, IFS(
 a<=1, FALSE,
 a<=3, TRUE,
 ISEVEN(a), FALSE,
 ISODD(a), LET(X, a/SEQUENCE(sqrt(a)-2,1,3), NOT(OR(X=INT(X))))
 )
 ),
Rng, A2:A10,
 FILTER(Rng, MAP(Rng, IsPrime))
)
Excel solution 11 for List Prime Numbers Only, proposed by Nazmul Islam Jobair: 
=FILTER(A2:A10,IFERROR(BYROW(A2:A10,LAMBDA(r,AND(SUM(--(MOD(r,SEQUENCE(r))=0))=2,r>1,INT(r)=r))),0))

Solving the challenge of List Prime Numbers Only with Python

Python solution 1 for List Prime Numbers Only, proposed by Kenneth Uchenna: 
1. import pandas
(data = pd.read_csv("primes.csv")
numbers.append(int(row["Number"]))
for num in numbers:
 if num > 1:
 for i in range(2, len(numbers)):
 if num % i == 0:
 print(num,"not prime")
 print(i, "times", num // i, "is", num)
 break
 else:
 print(num, "is a prime number")
 primes.append(num)
data["Answer"] = pd.Series(primes)

Solving the challenge of List Prime Numbers Only with Python in Excel

Python in Excel solution 1 for List Prime Numbers Only, proposed by Alejandro Campos: 
numbers = xl("A2:A10")[0]
df_primes = pd.DataFrame([n for n in numbers if n > 1 and all(n % i for i in range(2, int(n**0.5) + 1))], columns=['Prime Numbers'])

Leave a Reply