Generate A to Z letter sequence.

Provide a formula to generate the sequence shown (A till Z only)

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

Solving the challenge of Generate A to Z letter sequence with Power Query

Power Query solution 1 for Generate A to Z letter sequence, proposed by Matthias Friedmann:

let
  Source = {"A" .. "Z"}, 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    {"ABC"}, 
    null, 
    ExtraValues.Error
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Converted to Table", 
    "Custom", 
    each 
      let
        abc = [ABC], 
        number = Character.ToNumber(abc) - 64, 
        list = List.Transform(
          {1 .. 26}, 
          each if _ > 9 then abc & "-" & Text.From(_) else abc & "-0" & Text.From(_)
        )
      in
        List.Transform(list, each if Number.From(Text.End(_, 2)) > number then null else _)
  ), 
  #"Extracted Values" = Table.TransformColumns(
    #"Added Custom", 
    {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}
  ), 
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Extracted Values", 
    "Custom", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    List.Transform({1 .. 26}, each Text.From(_))
  )
in
  #"Split Column by Delimiter"

Power Query solution 2 for Generate A to Z letter sequence, proposed by Matthias Friedmann:

if _>9 then abc &"-"&Text.From(_) 
else abc &"-0"&Text.From(_) 
abc &"-"& Text.PadStart(Text.From(_), 2, "0")

Power Query solution 3 for Generate A to Z letter sequence, proposed by Victor Wang:

let
  CreateCols = List.Accumulate(
    {0 .. 25}, 
    {}, 
    (state, current) =>
      state
        & {
          List.Repeat({null}, current)
            & List.RemoveFirstN(
              List.Transform(
                {"A" .. "Z"}, 
                each _ & "-" & Text.PadStart(Text.From(current + 1), 2, "0")
              ), 
              current
            )
        }
  ), 
  MakeTable = Table.FromColumns(CreateCols)
in
  MakeTable

Power Query solution 4 for Generate A to Z letter sequence, proposed by Venkata Rajesh:

let
  Source = Table.RenameColumns(
    Table.FromList(
      List.Transform({"A" .. "Z"}, each _ & "-" & "01"), 
      Splitter.SplitByNothing(), 
      null, 
      null, 
      ExtraValues.Error
    ), 
    {{"Column1", "Column0"}}
  ), 
  Output = Table.ExpandTableColumn(
    Table.AddColumn(
      Source, 
      "Custom", 
      each 
        let
          _char = Text.Start([Column0], 1)
        in
          Table.Transpose(
            Table.FromList(
              List.Transform(
                {2 .. Table.PositionOf(Source, _) + 1}, 
                each _char & "-" & Text.PadStart(Text.From(_), 2, "0")
              ), 
              Splitter.SplitByNothing(), 
              null, 
              null, 
              ExtraValues.Error
            )
          )
    ), 
    "Custom", 
    List.Transform({1 .. 25}, each "Column" & Text.From(_)), 
    List.Transform({1 .. 25}, each "Column" & Text.From(_))
  ), 
  Result = Table.RenameColumns(
    Output, 
    List.Zip({Table.ColumnNames(Output), List.Transform({1 .. 26}, each "Column" & Text.From(_))})
  )
in
  Result

Power Query solution 5 for Generate A to Z letter sequence, proposed by Melissa de Korte:

let
  s = {"A" .. "Z"}, 
  a = List.Transform(
    s, 
    (x) =>
      List.Transform(
        {1 .. List.PositionOf(s, x) + 1}, 
        (y) =>
          Record.FromList({x & "-" & Text.PadStart(Text.From(y), 2, "0")}, {"Col " & Text.From(y)})
      )
  ), 
  t = Table.Combine(
    List.Transform(List.Transform(a, each Record.Combine(_)), each Table.FromRecords({_}))
  )
in
  t

Power Query solution 6 for Generate A to Z letter sequence, proposed by Melissa de Korte:

let
  s = {"A" .. "Z"}, 
  a = List.TransformMany(
    s, 
    each {
      Record.Combine(
        List.Transform(
          {1 .. List.PositionOf(s, _) + 1}, 
          (y) =>
            Record.FromList(
              {_ & "-" & Text.PadStart(Text.From(y), 2, "0")}, 
              {"Col " & Text.From(y)}
            )
        )
      )
    }, 
    (x, y) => Table.FromRecords({y})
  ), 
  t = Table.Combine(a)
in
  t

Power Query solution 7 for Generate A to Z letter sequence, proposed by Hristo Tsenov:

let
  Source = {"A" .. "Z"}, 
  Numbers = Table.AddIndexColumn(
    Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
    "Index", 
    1, 
    1, 
    Int64.Type
  ), 
  Combine = Table.AddColumn(
    Table.ExpandListColumn(Table.AddColumn(Numbers, "Custom", each {1 .. [Index]}), "Custom"), 
    "Merged", 
    each Text.Combine({[Column1], Text.From([Custom], "en-US")}, "-"), 
    type text
  ), 
  Result = Table.RemoveColumns(
    Table.Pivot(
      Table.TransformColumnTypes(
        Table.RemoveColumns(Combine, {"Index"}), 
        {{"Custom", type text}}, 
        "en-US"
      ), 
      List.Distinct(Table.TransformColumnTypes(Combine, {{"Custom", type text}}, "en-US")[Custom]), 
      "Custom", 
      "Merged"
    ), 
    {"Column1"}
  )
in
  Result

Solving the challenge of Generate A to Z letter sequence with Excel

Excel solution 1 for Generate A to Z letter sequence, proposed by Rick Rothstein:

=MAKEARRAY(26,
    26,
    LAMBDA(r,
    c,
    LEFT(CHAR(
        64+r
    )&TEXT(
        c,
        "-00"
    ),
    4*(r>=c))))

Excel solution 2 for Generate A to Z letter sequence, proposed by John V.:

=MAKEARRAY(
    26,
    26,
    LAMBDA(
        r,
        c,
        REPT(
            CHAR(
                64+r
            )&TEXT(
                c,
                "-00"
            ),
            r>=c
        )
    )
)

Excel solution 3 for Generate A to Z letter sequence, proposed by محمد حلمي:

=INDEX(
    INDEX(
        CHOOSECOLS(
            CHAR(
                
                SEQUENCE(
                    26,
                    ,
                    65
                )
            ),
            SEQUENCE(
                ,
                26,
                ,
                0
            )
        )&
        TEXT(
            SEQUENCE(
                ,
                26
            ),
            "-00"
        ),
        ROW(
            A1
        )
    ),
    
    SEQUENCE(
        ,
        ROW(
            A1
        )
    )
)

Excel solution 4 for Generate A to Z letter sequence, proposed by محمد حلمي:

=
LET(
    b,
    SEQUENCE(
        ,
        26
    ),
    c,
    SEQUENCE(
        26,
        ,
        0,
        0
    ),
    
    IF(
        b<=TOCOL(
            b
        ),
        CHAR(
            SEQUENCE(
                ,
                26,
                65
            )+c
        )
        &"-"&TEXT(
            b+c,
            "00"
        ),
        ""
    )
)

Excel solution 5 for Generate A to Z letter sequence, proposed by Aditya Kumar Darak 🇮🇳:

= MAKEARRAY(
    
     26,
    
     26,
    
     LAMBDA(
         r,
          c,
          IF(
              c > r,
               "",
               CHAR(
                   64 + r
               ) & TEXT(
                   c,
                    "-00"
               )
          )
     )
)

Excel solution 6 for Generate A to Z letter sequence, proposed by Aditya Kumar Darak 🇮🇳:

= LET(
    
     _array,
    
     MOD(
         SEQUENCE(
             26,
              26
         ) - 1,
          26
     ) + 1,
    
     IF(
         _array > TRANSPOSE(
             _array
         ),
         
          "",
         
          CHAR(
              64 + TRANSPOSE(
             _array
         )
          )
          & TEXT(
              _array,
               "-00"
          )
     )
)

Excel solution 7 for Generate A to Z letter sequence, proposed by Aditya Kumar Darak 🇮🇳:

= IFERROR(
 DROP(
 REDUCE(
 "",
 SEQUENCE(26),
 LAMBDA(
 a,
 b,
 VSTACK(
 a,
 CHAR(64 + b) & "-" & TEXT(SEQUENCE(1, b), "00")))),
 1),
 "")

Excel solution 8 for Generate A to Z letter sequence, proposed by Oscar Mendez Roca Farell:

=IFERROR(CHAR(ROW(
    A65
)*(ROW(
    A1
)>=COLUMN(
    A1
)))&"-"&TEXT((ROW(
    A1
)>=COLUMN(
    A1
))*COLUMN(
    A1
),
    "00"),
    "")

Or alternatively selecting range A1:Z26 and pressing CTRL+SHIFT+ENTER:
=IFERROR(CHAR(ROW(
    A65:A90
)*(ROW(
    A1:Z26
)>=COLUMN(
    A1:Z26
)))&"-"&TEXT((ROW(
    A1:Z26
)>=COLUMN(
    A1:Z26
))*COLUMN(
    A1:Z26
),
    "00"),
    "")

Excel solution 9 for Generate A to Z letter sequence, proposed by Jaroslaw Kujawa:

=IF(
    ROW() less than COLUMN(),
    "",
    CHAR(
        64+ROW()
    )&"-"&TEXT(
        COLUMN(),
        "00"
    )
)

Excel solution 10 for Generate A to Z letter sequence, proposed by Jardiel Euflázio:

=LET(
    
    
    a,
    SEQUENCE(
        26
    ),
    
    b,
    SEQUENCE(
        ,
        26
    ),
    
    
    IF(
        a>=b,
        
        CHAR(
            SEQUENCE(
                26,
                ,
                65
            )
        )&TEXT(
            b,
            "-00"
        ),
        
        ""
    )
    
    
)

Excel solution 11 for Generate A to Z letter sequence, proposed by Victor Momoh (MVP, MOS, R.Eng):

=MAKEARRAY(
    26,
    26,
    LAMBDA(
        r,
        c,
        IF(
            c>r,
            "",
            CHAR(
                64+r
            )&"-"&TEXT(
                c,
                "00"
            )
        )
    )
)

Excel solution 12 for Generate A to Z letter sequence, proposed by Cary Ballard, DML:

=MAKEARRAY(
    26,
     26,
     LAMBDA(
         r,
          c,
          IF(
              c > r,
               "",
               CHAR(
                   r + 64
               ) & TEXT(
                   c,
                    "-00"
               )
          )
     )
)

Excel solution 13 for Generate A to Z letter sequence, proposed by Cary Ballard, DML:

=LET(
 a, SEQUENCE(26, , 65),
 b, SEQUENCE(, 26, 65),
 c, IF(a >= b, a),
 IF(c, CHAR(c) & "-" & TEXT(b, "00"), "")
)

Excel solution 14 for Generate A to Z letter sequence, proposed by Juliano Santos Lima:

=LAMBDA(
    Row,
    Col,
    IF(
        Row

Excel solution 15 for Generate A to Z letter sequence, proposed by Riley Johnson:

= LAMBDA(_generate,
 LET(
 _ALPHA_NUM, 26,
 _ALPHA,  CODE("A"),

 if_zero, LAMBDA(x,y, IF( x = 0, y, x) ),

 stacker, LAMBDA(stack,_index,
 LET(
 _row, SEQUENCE(,_index ),
 _char, REPT(
 CHAR( if_zero( MOD( _index, _ALPHA_NUM ), _ALPHA_NUM ) + _ALPHA - 1 ),
 INT( ( _index - 1 ) / _ALPHA_NUM ) + 1
 ),

 _result, VSTACK(
 EXPAND( stack, ,_index, "" ),
 _char & "-" & IF( LEN(_row) < 2, "0" & _row, _row )
 ),

 _result
 )
 ),

 firstrow, IF( _generate > 0, "A" & "-" & "01", "" ),

 IF( _generate > 1,
 REDUCE( firstrow, DROP( SEQUENCE( _generate ), 1 ),
 stacker
 ),
 firstrow
 )
 )
)( 26 * 3 )

Excel solution 16 for Generate A to Z letter sequence, proposed by Nicolas Brabant:

=MAKEARRAY(
    26;
    26;
    LAMBDA(
        l;
        c;
        SI(
            l>=c;
            CAR(
                l+64
            )&TEXTE(
                c;
                "-00"
            );
            ""
        )
    )
)

Excel solution 17 for Generate A to Z letter sequence, proposed by sharad Gupta:

=IF(
    COLUMN()>ROW(
        A1
    ),
    "",
    CHAR(
        64+ROW(
        A1
    )
    )&"-0"&COLUMN()
)

Excel solution 18 for Generate A to Z letter sequence, proposed by N’rele Ferdinand Attobra:

=CHAR(
    64+ROW()
)&TEXT(
    SEQUENCE(
        ,
        ROW()
    ),
    "-00"
)

Excel solution 19 for Generate A to Z letter sequence, proposed by Romeo Costillas:

=IF(
    ROW()>=COLUMN(),
    CHAR(
        ROW()+64
    )&"-"&TEXT(
        COLUMN(),
        "00"
    ),
    ""
)

Solving the challenge of Generate A to Z letter sequence with Power Query

Solving the challenge of Generate A to Z letter sequence with Excel

Leave a Reply Cancel reply