Home » List Alphabet And Numbers

List Alphabet And Numbers

List all consecutive alphabets in Data column separated by comma. Against this, list all numbers separated by comma between this set of alphabets and next alphabet.

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

Solving the challenge of List Alphabet And Numbers with Power Query

Power Query solution 1 for List Alphabet And Numbers, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.Group(
    A, 
    "Data", 
    {"All", each [Data]}, 
    0, 
    (x, y) => Number.From(Value.Type(x) <> Value.Type(y))
  ), 
  C = Table.Combine(
    Table.Group(
      B, 
      "Data", 
      {
        "2Gr", 
        each [
          a = (z) => try Text.Combine(List.Transform([All]{z}, Text.From), ", ") otherwise null, 
          b = Table.FromRows({{a(0), a(1)}}, {"Alphabet", "Number"})
        ][b]
      }, 
      0, 
      (v, w) => Number.From(w is text)
    )[2Gr]
  )
in
  C
Power Query solution 2 for List Alphabet And Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Calc = Table.Group(
    Source, 
    "Data", 
    {
      "A", 
      each 
        let
          a = _, 
          b = List.Transform(a[Data], Text.From), 
          c = Text.Combine(b, ", ")
        in
          c
    }, 
    0, 
    (x, y) => Number.From(Value.Type(y) <> Value.Type(x))
  ), 
  Sol = Table.FromColumns(
    {
      Table.SelectRows(Calc, each [Data] is text)[A], 
      Table.SelectRows(Calc, each [Data] is number)[A]
    }, 
    {"Alphabet", "Number"}
  )
in
  Sol
Power Query solution 3 for List Alphabet And Numbers, proposed by Ramiro Ayala Chávez: 
let
  S   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a   = Table.AddColumn(S, "A", each if [Data] is text then 0 else 1), 
  b   = Table.Group(a, "A", {"G", each Text.Combine(List.Transform([Data], Text.From), ", ")}, 0)[G], 
  c   = List.Zip({List.Alternate(b, 1, 1, 1), List.Alternate(b, 1, 1)}), 
  Sol = Table.FromRows(c, {"Alphabet", "Number"})
in
  Sol
Power Query solution 4 for List Alphabet And Numbers, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data], 
  Lst = [
    A = List.Accumulate(
      List.Skip(Source), 
      {{Source{0}}}, 
      (a, v) =>
        if Value.Type(v) = Value.Type(List.Last(List.Last(a))) then
          List.RemoveLastN(a) & {List.Last(a) & {v}}
        else
          a & {{v}}
    ), 
    B = List.Transform(A, each Text.Combine(List.Transform(_, each Text.From(_)), ", "))
  ][B], 
  Cols = {List.Alternate(Lst, 1, 1, 1), List.Alternate(Lst, 1, 1, 0)}, 
  Res = Table.FromColumns(Cols, {"Alphabet", "Number"})
in
  Res
Power Query solution 5 for List Alphabet And Numbers, proposed by Seokho MOON: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group  = Table.Group(Source, "Data", {"txt", Fun_1}, 0, Fun_2)[txt], 
  Fun_1  = each [A = List.Transform([Data], each Text.From(_)), B = Text.Combine(A, ", ")][B], 
  Fun_2  = (x, y) => Number.From(Value.Type(x) <> Value.Type(y)), 
  Cols   = {List.Alternate(Group, 1, 1, 1), List.Alternate(Group, 1, 1, 0)}, 
  Res    = Table.FromColumns(Cols, {"Alphabet", "Number"})
in
  Res
Power Query solution 6 for List Alphabet And Numbers, proposed by Meganathan Elumalai: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.FromColumns(
    List.Zip(
      List.Split(
        Table.Group(
          Source, 
          "Data", 
          {{"New", each Text.Combine(List.Transform(_[Data], Text.From), ", ")}}, 
          0, 
          (x, y) => Number.From(Value.Type(x) <> Value.Type(y))
        )[New], 
        2
      )
    ), 
    {"Alphabet", "Number"}
  )
in
  Result
Power Query solution 7 for List Alphabet And Numbers, proposed by Mihai Radu O: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  s = [
    lt = List.Transform, 
    ls = List.Select, 
    tc = Text.Combine, 
    l = lt(Source[Data], Text.From), 
    a = tc(l, "|"), 
    fct = (char as list) =>
      lt(
        ls(Text.SplitAny(a, tc(char)), (x) => x <> "|" and x <> ""), 
        (z) => tc(ls(Text.Split(z, "|"), each _ <> ""), ", ")
      ), 
    tbl = Table.FromColumns({fct({"0" .. "9"}), fct({"a" .. "z"})}, {"Alphabet", "Number"})
  ][tbl]
in
  s
Power Query solution 8 for List Alphabet And Numbers, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Grp = Table.Group(
    Source, 
    "Data", 
    {
      {
        "tmp", 
        each try
          Text.Combine([Data], ", ")
        otherwise
          Text.Combine(List.Transform([Data], Text.From), ", ")
      }
    }, 
    0, 
    (x, y) => Number.From(Value.Type(y) <> Value.Type(x))
  )[tmp], 
  Result = Table.FromColumns(
    {List.Alternate(Grp, 1, 1, 1), List.Alternate(Grp, 1, 1, 0)}, 
    {"Alphabet", "Numbers"}
  )
in
  Result

Solving the challenge of List Alphabet And Numbers with Excel

Excel solution 1 for List Alphabet And Numbers, proposed by Bo Rydobon 🇹🇭: 
=LET(z,
    A3:A22,
    TEXTSPLIT(TEXTJOIN(SWITCH((DROP(
        z,
        -1
    )>"")*3+(DROP(
        z,
        1
    )>""),
    3,
    "-",
    1,
    "/",
    ", "),
    ,
    z),
    "-",
    "/",
    1,
    ,
    ""))
Excel solution 2 for List Alphabet And Numbers, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A3:A22,TEXTSPLIT(TEXTJOIN(SWITCH((DROP(z,-1)>"")*9+(DROP(z,1)>""),9,"-",1,"/",", "),,z),"-","/",1,,""))
Excel solution 3 for List Alphabet And Numbers, proposed by 🇰🇷 Taeyong Shin: 
=LET(
 c, A3:A22,
 b, c > "",
 L, LAMBDA(f, LAMBDA(x, TEXTJOIN(", ", , REPT(x, f(x))))),
 DROP(GROUPBY(SCAN(0, b > DROP(VSTACK(0, b), -1), SUM), c,
 HSTACK(L(ISTEXT), L(ISNUMBER)), 0, 0), 1, 1)
)
Excel solution 4 for List Alphabet And Numbers, proposed by Kris Jaganah: 
=LET(a,A3:A22,b,ISNUMBER(a),c,SCAN(0,b,SUM),d,ISTEXT(a),e,UNIQUE(c*d),REDUCE({"Alphabet","Number"},e,LAMBDA(x,y,VSTACK(x,HSTACK(ARRAYTOTEXT(FILTER(a,(c=y)*d)),ARRAYTOTEXT(FILTER(a,(c>y)*(c<=XLOOKUP(y+1,e,e,"",1))*b,"")))))))
Excel solution 5 for List Alphabet And Numbers, proposed by Timothée BLIOT: 
=LET(
    A,
    A3:A22,
    B,
    LAMBDA(
        m,
        SCAN(
            0,
            A,
            LAMBDA(
                w,
                v,
                IF(
                    m(
                        v
                    ),
                    w+1,
                    0
                )
            )
        )
    ),
    C,
    LAMBDA(
        n,
        IF(
            n={1,
            2,
            3},
            A,
            ""
        )
    ),
    D,
    C(
        B(
            ISNUMBER
        )
    ),
    E,
    C(
        B(
            ISTEXT
        )
    ),
     F,
    LAMBDA(
        l,
        IFNA(
            HSTACK(
                TAKE(
                    l,
                    ,
                    1
                ),
                DROP(
                    INDEX(
                        l,
                        ,
                        2
                    ),
                    1
                ),
                DROP(
                    TAKE(
                        l,
                        ,
                        -1
                    ),
                    2
                )
            ),
            ""
        )
    ),
    G,
    LAMBDA(
        o,
        BYROW(
            o,
            LAMBDA(
                x,
                TEXTJOIN(
                    ", ",
                    ,
                    x
                )
            )
        )
    ),
    IFNA(
        HSTACK(
             FILTER(
                 G(
                     F(
                         E
                     )
                 ),
                 G(
                     F(
                         E
                     )
                 )<>""
             ),
            FILTER(
                G(
                    F(
                        D
                    )
                ),
                G(
                    F(
                        D
                    )
                )<>""
            )
        ),
        ""
    )
)
Excel solution 6 for List Alphabet And Numbers, proposed by Hussein SATOUR: 
=WRAPROWS(REDUCE(,A3:A22,LAMBDA(x,y,LET(V,VSTACK,a,TAKE(x,-1),c,CODE(a),d,CODE(y),IF(OR((c>96)*(d>96),(c<96)*(d<96)),V(DROP(x,-1),a&", "&y),V(x,y))))),2,"")
Excel solution 7 for List Alphabet And Numbers, proposed by Oscar Mendez Roca Farell: 
=LET(d,
    A3:A22,
    n,
    (N(
        +DROP(
            d,
            -1
        )
    )>0)+(DROP(
        d,
        1
    )>""),
    TEXTSPLIT(
         TEXTJOIN(
             IFS(
                 n=1,
                 ", ",
                 n=2,
                 "|",
                 1,
                 "/"
             ),
             ,
             d
         ),
        "/",
        "|",
        1,
        ,
        ""
    ))
Excel solution 8 for List Alphabet And Numbers, proposed by Sunny Baggu: 
=LET(
 rng, A3:A22,
 IFNA(
 HSTACK(
 TEXTBEFORE(
 TEXTSPLIT(CONCAT(IF(ISERR(--rng), rng & ", ", "/")), , "/", 1),
 ", ",
 -1
 ),
 TEXTBEFORE(
 TEXTSPLIT(CONCAT(IF(NOT(ISERR(--rng)), rng & ", ", "/")), , "/", 1),
 ", ",
 -1
 )
 ),
 ""
 )
)
Excel solution 9 for List Alphabet And Numbers, proposed by Md. Zohurul Islam: 
=LET(z,A3:A22,u,ISTEXT(z),v,ISNUMBER(z),
f,LAMBDA(x,DROP(GROUPBY(DROP(x,,-1),TAKE(x,,-1),ARRAYTOTEXT,0,0),,1)),
w,LAMBDA(x,y,FILTER(HSTACK(SCAN(1,IF(x,0,1),SUM),y),x)),
a,IFNA(HSTACK(f(w(u,z)),f(w(v,z))),""),
b,VSTACK(HSTACK("Alphabet","Number"),a),
b)
Excel solution 10 for List Alphabet And Numbers, proposed by Hamidi Hamid: 
=LET(at,A3:A22,z,IFERROR(at*1,""),hh,IF(ISTEXT(at),at,""),j,SCAN(,hh,LAMBDA(a,b,IF(b="",a,b))),h,MAP(UNIQUE(j),LAMBDA(a,TEXTJOIN(",",1,FILTER(z,j=a)))),r,FILTER(h,h<>""),g,SCAN(,z,LAMBDA(a,b,IF(b="",a,b))),k,MAP(UNIQUE(g),LAMBDA(a,TEXTJOIN(",",1,TEXTJOIN(",",1,FILTER(hh,g=a))))),p,FILTER(k,k<>""),IFERROR(HSTACK(p,r),""))
Excel solution 11 for List Alphabet And Numbers, proposed by Asheesh Pahwa: 
=LET(
    a,
    ARRAYTOTEXT(
        A3:A22
    ),
    _t1,
    TEXTSPLIT(
        a,
        CHAR(
            SEQUENCE(
                26,
                ,
                97
            )
        ),
        ,
        1
    ),
    f,
    TOCOL(
        FILTER(
            _t1,
            _t1<>", "
        )
    ),
    _t2,
    TEXTSPLIT(
        a,
        SEQUENCE(
            10,
            ,
            0
        ),
        ,
        1
    ),
    
    _f1,
    TOCOL(
        FILTER(
            _t2,
            _t2<>", "
        )
    ),
    
    h,
    HSTACK(
        _f1,
        f
    ),
    IFNA(
        MAP(
            h,
            LAMBDA(
                x,
                ARRAYTOTEXT(
                    TEXTSPLIT(
                        x,
                        ", ",
                        ,
                        1
                    )
                )
            )
        ),
        ""
    )
)
Excel solution 12 for List Alphabet And Numbers, proposed by ferhat CK: 
=LET(r,A3:A22,b,SCAN(0,r,LAMBDA(a,v,IFS(ISNUMBER(v),a,(ISTEXT(v))*(ISTEXT(OFFSET(v,-1,))),a,1=1,a+1))),REDUCE({"Alphabet","Number"},UNIQUE(b),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(r,b=y),BYCOL(IFNA(REGEXEXTRACT(f,{"D+","d+"},1),""),LAMBDA(v,TEXTJOIN(",",1,v))))))))
Excel solution 13 for List Alphabet And Numbers, proposed by Ankur Sharma: 
=LET(s, SUBSTITUTE, d, A3:A22,
a, IF(ISTEXT(d), ":" & d & ":", "-" & d & ";"),
r, CONCAT(a),
rm, s(s(r, ";-", ", "), "::", ", "),
s(s(TEXTSPLIT(rm, ":-", ";:", TRUE, , ""), ":", ""), ";", ""))
Excel solution 14 for List Alphabet And Numbers, proposed by JvdV -: 
=TEXTSPLIT(REGEXREPLACE(TEXTJOIN(",",,A3:A22),",(d+(,d+)*)(,)?",".$1${3:+|}"),".","|",,,"")
Excel solution 15 for List Alphabet And Numbers, proposed by Imam Hambali: 
=LET(
d, A3:A22,
it, ISTEXT(d)*1,
in, ISNUMBER(d)*1,
l, LAMBDA(x, FILTER(HSTACK(SCAN(1, x, SUM),d), IF(x=in,it, in))),
cc, CHOOSECOLS,
lg, LAMBDA(x, DROP(GROUPBY(cc(x,1), cc(x,2),ARRAYTOTEXT,0,0),,1)),
VSTACK({"Alphabet","Number"}, IFERROR(HSTACK(lg(l(in)),lg(l(it))),""))
)
Excel solution 16 for List Alphabet And Numbers, proposed by El Badlis Mohd Marzudin: 
=LET(
    w,
    A3:A22,
    t,
    w>"",
    n,
    w<"",
    f,
    LAMBDA(
        x,
        LET(
            a,
            TEXTSPLIT(
                CONCAT(
                    IF(
                        x,
                        w&", ",
                        "|"
                    )
                ),
                ,
                "|",
                1
            ),
            LEFT(
                a,
                LEN(
                    a
                )-2
            )
        )
    ),
    IFNA(
        HSTACK(
            f(
                t
            ),
            f(
                n
            )
        ),
        ""
    )
)

Solving the challenge of List Alphabet And Numbers with Python

Python solution 1 for List Alphabet And Numbers, proposed by Konrad Gryczan, Ph&D: 
import pandas as pd
import re
path = "642 List Alphabets and Numbers.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=21)
test = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=6).fillna("").astype(str)
input['type'] = input.iloc[:, 0].apply(lambda x: 'Alphabet' if re.match(r'[A-Za-z]', str(x)) else 'Number')
input['consecutive_id'] = (input['type'] != input['type'].shift()).cumsum()
input['Data'] = input['Data'].astype(str)
result = input.groupby('consecutive_id').agg({'Data': ', '.join, 'type': 'first'}).reset_index(drop=True)
result['group'] = result.index // 2
pivot_result = result.pivot(index='group', columns='type', values='Data').reset_index(drop=True).fillna("")
pivot_result.columns.name = None
print(pivot_result.equals(test)) # True

Solving the challenge of List Alphabet And Numbers with Python in Excel

Python in Excel solution 1 for List Alphabet And Numbers, proposed by Aditya Kumar Darak 🇮🇳: 
df = xl("A2:A22", True)
t = df["Data"].map(type)
grp = (
 df.groupby((t != t.shift()).cumsum())["Data"]
 .apply(lambda x: ", ".join(map(str, x)))
 .values
)
result = grp.reshape(-1, 2) if len(grp) % 2 == 0 else np.append(grp, "").reshape(-1, 2)
result

Solving the challenge of List Alphabet And Numbers with Excel VBA

Excel VBA solution 1 for List Alphabet And Numbers, proposed by Andris Platais: 
My VBA code (run it form empty cell)
Sub Chalange_642()
intRowN = ActiveCell.Row + 1
intRowL = ActiveCell.Row + 1
intColL = ActiveCell.Column
intColN = intColL + 1
bolLastIsN = False
Cells(ActiveCell.Row, ActiveCell.Column) = "Alphabet" 'add headings
Cells(ActiveCell.Row, ActiveCell.Column + 1) = "Number"
For i = 3 To 22
 If Not IsNumeric(Cells(i, 1)) Then 'Check if cell is Alphabet
 Cells(intRowL, intColL) = Cells(i, 1).Value
 Else
 Cells(intRowL, intColL) = Cells(intRowL, intColL) & ", " & Cells(i, 1).Value
 End If
 bolLastIsN = False
 Else
 Cells(intRowN, intColN) = Cells(i, 1).Text
 Else
 Cells(intRowN, intColN) = Cells(intRowN, intColN) & ", " & Cells(i, 1).Value
 End If
 bolLastIsN = True
 End If
Next i
End Sub

&&

Leave a Reply