Sum Values by Letter Codes

Generate the result table from problem table. Quantity in result table is sum of number appearing next to the letter in problem table.

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

Solving the challenge of Sum Values by Letter Codes with Power Query

Power Query solution 1 for Sum Values by Letter Codes, proposed by Omid Motamedisedeh:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = [
    A = Source[String], 
    B = List.Combine(List.Transform(A, each Text.Split(Text.Remove(_, {"A" .. "Z"}), " "))), 
    C = List.Combine(List.Transform(A, each Text.Split(Text.Select(_, {"A" .. "Z", " "}), " "))), 
    D = Table.TransformColumnTypes(
      Table.FromColumns(
        {List.Select(C, each _ <> ""), List.Select(B, each _ <> "")}, 
        {"Letters", "Quantity"}
      ), 
      {{"Quantity", Int64.Type}}
    )
  ][D], 
  #"Grouped Rows" = Table.Group(
    Custom1, 
    {"Letters"}, 
    {{"Quantity", each List.Sum([Quantity]), type nullable number}}
  ), 
  #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Letters", Order.Ascending}})
in
  #"Sorted Rows"

Power Query solution 2 for Sum Values by Letter Codes, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = 
    let
      z = Text.Combine(Source[String]), 
      a = List.Select(Text.SplitAny(z, "01234568789 "), each _ > "")
    in
      Table.Sort(
        Table.Group(
          Table.FromColumns(
            {
              a, 
              List.Transform(
                List.Select(Splitter.SplitTextByAnyDelimiter(a)(z), each _ > ""), 
                Number.From
              )
            }, 
            {"Letters", "Q"}
          ), 
          "Letters", 
          {"Quantity", each List.Sum([Q])}
        ), 
        "Letters"
      )
in
  Ans

Power Query solution 3 for Sum Values by Letter Codes, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  ConvertToTable = Table.FromRows(
    List.Split(
      List.Accumulate(
        Text.ToList(Text.Remove(Text.Combine(Source[String]), " ")), 
        {}, 
        (s, c) =>
          if Value.FromText(List.Last(s)) is number and Value.FromText(c) is number then
            List.RemoveLastN(s, 1) & {List.Last(s) & c}
          else
            s & {c}
      ), 
      2
    ), 
    {"Letters", "Quantity"}
  ), 
  Solution = Table.Sort(
    Table.Group(
      ConvertToTable, 
      {"Letters"}, 
      {{"Quantity", each List.Sum(List.Transform([Quantity], Number.From))}}
    ), 
    "Letters"
  )
in
  Solution

Power Query solution 4 for Sum Values by Letter Codes, proposed by 🇰🇷 Taeyong Shin:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content], 
  ReplaceValue = Table.ReplaceValue(
    Source, 
    each [String], 
    each List.Accumulate({"0" .. "9"}, [String], (a, c) => Text.Replace(a, c & " ", c & ", ")), 
    Replacer.ReplaceText, 
    {"String"}
  ), 
  Split = Table.ExpandListColumn(
    Table.TransformColumns(ReplaceValue, {"String", Splitter.SplitTextByDelimiter(", ")}), 
    "String"
  ), 
  AddCol = Table.AddColumn(Split, "Letter", each Text.Start([String], 1), type text), 
  Group = Table.Group(
    AddCol, 
    "Letter", 
    {
      "Quantity", 
      each List.Sum(List.Transform([String], each Number.From(Text.RemoveRange(_, 0, 1)))), 
      Int64.Type
    }
  ), 
  SortTbl = Table.Sort(Group, {"Letter", 0})
in
  SortTbl

Power Query solution 5 for Sum Values by Letter Codes, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Split Column by Character Transition" = Table.SplitColumn(
    Source, 
    "String", 
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)), 
    {"String - Copy.1", "String - Copy.2", "String - Copy.3", "String - Copy.4", "String - Copy.5"}
  ), 
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(
    #"Split Column by Character Transition", 
    {}, 
    "Attribute", 
    "Value"
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns", {"Attribute"}), 
  #"Added Custom" = Table.AddColumn(#"Removed Columns", "Letters_1", each Text.Trim([Value])), 
  #"Split Column by Position" = Table.SplitColumn(
    #"Added Custom", 
    "Letters_1", 
    Splitter.SplitTextByPositions({0, 1}, false), 
    {"Letters", "Qty"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position", {{"Qty", Int64.Type}}), 
  #"Removed Columns1" = Table.RemoveColumns(#"Changed Type", {"Value"}), 
  #"Grouped Rows" = Table.Group(
    #"Removed Columns1", 
    {"Letters"}, 
    {{"Quantity", each List.Sum([Qty]), type nullable number}}
  ), 
  #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Letters", Order.Ascending}})
in
  #"Sorted Rows"

Power Query solution 6 for Sum Values by Letter Codes, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Record = Table.AddColumn(
    Source, 
    "R", 
    each [
      S = Splitter.SplitTextByCharacterTransition({"0" .. "9", " "}, {"A" .. "Z"})([String]), 
      T = List.Transform(
        S, 
        (f) => {Text.Select(f, {"A" .. "Z"})} & {Number.From(Text.Select(f, {"0" .. "9"}))}
      )
    ][T]
  ), 
  Table = Table.FromRows(List.Combine(Record[R]), {"Letter", "Q"}), 
  Group = Table.Group(Table, "Letter", {"Quantity", each List.Sum([Q])}), 
  Return = Table.Sort(Group, "Letter")
in
  Return

Power Query solution 7 for Sum Values by Letter Codes, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Col = Table.Combine(
    Table.AddColumn(
      Source, 
      "Custom", 
      each 
        let
          a = Text.Replace([String], " ", ""), 
          b = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})(a), 
          c = List.Transform(
            b, 
            each {Text.ToList(_){0}} & {Text.Combine(List.Skip(Text.ToList(_)))}
          ), 
          d = Table.FromRows(c)
        in
          d
    )[Custom]
  ), 
  Group = Table.Sort(
    Table.Group(
      Col, 
      {"Column1"}, 
      {
        {
          "Quality", 
          each 
            let
              a = [Column2], 
              b = List.Sum(List.Transform(a, Number.From))
            in
              b
        }
      }
    ), 
    "Column1"
  ), 
  Sol = Table.RenameColumns(Group, {{"Column1", "Letters"}})
in
  Sol

Power Query solution 8 for Sum Values by Letter Codes, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "lista", 
    each [
      a = Text.Replace([String], " ", ""), 
      b = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})(a), 
      c = List.Combine({b})
    ][c]
  )[[lista]], 
  exp = Table.ExpandListColumn(add, "lista"), 
  div = Table.SplitColumn(
    exp, 
    "lista", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Letters", "Quantity"}
  ), 
  gp = Table.Group(
    div, 
    {"Letters"}, 
    {{"Quantity", each List.Sum(List.Transform([Quantity], Number.From))}}
  ), 
  res = Table.Sort(gp, {{"Letters", Order.Ascending}})
in
  res

Power Query solution 9 for Sum Values by Letter Codes, proposed by Hussein SATOUR:

 #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByCharacterTransition(
{"0".."9"}, (c) => not List.Contains({"0".."9"}, c))([String])),
 #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
 #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom"," ","",Replacer.ReplaceText,{"Custom"}),
 #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Custom", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Custom.2"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Custom.2", Int64.Type}}),
 #"Grouped Rows" = Table.Group(#"Changed Type1", {"Letters"}, {{"Quantity", each List.Sum([Custom.2]), type nullable text}}),
 #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Letters", Order.Ascending}})
in
 #"Sorted Rows"

Power Query solution 10 for Sum Values by Letter Codes, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Duplicated Column" = Table.DuplicateColumn(Source, "String", "Anchor"), 
  RemoveSpaces = Table.ReplaceValue(#"Duplicated Column", " ", "", Replacer.ReplaceText, {"String"}), 
  #"Split Column by Character Transition" = Table.SplitColumn(
    RemoveSpaces, 
    "String", 
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)), 
    {"String.1", "String.2", "String.3", "String.4", "String.5"}
  ), 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Split Column by Character Transition", 
    {"Anchor"}, 
    "Attribute", 
    "Value"
  ), 
  #"Split Column by Character Transition1" = Table.SplitColumn(
    #"Unpivoted Other Columns", 
    "Value", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Letter", "Quantity"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Split Column by Character Transition1", 
    {{"Quantity", Int64.Type}}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Letter"}, 
    {{"Sum", each List.Sum([Quantity]), type nullable number}}
  ), 
  #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Letter", Order.Ascending}})
in
  #"Sorted Rows"

Power Query solution 11 for Sum Values by Letter Codes, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData77"]}[Content], 
  Transform = Table.TransformColumns(
    Source, 
    {
      "String", 
      each 
        let
          _S     = Text.Replace(_, " ", ""), 
          _Split = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})(_S)
        in
          List.Transform(
            _Split, 
            each Record.FromList(
              Splitter.SplitTextByCharacterTransition({"A" .. "Z"}, {"0" .. "9"})(_), 
              {"Letters", "Value"}
            )
          )
    }
  ), 
  Expand = Table.ExpandRecordColumn(
    Table.ExpandListColumn(Transform, "String"), 
    "String", 
    {"Letters", "Value"}
  ), 
  ChangeType = Table.TransformColumnTypes(Expand, {{"Value", Int64.Type}}), 
  Group = Table.Group(ChangeType, {"Letters"}, {{"Quantity", each List.Sum([Value])}}), 
  Sort = Table.Sort(Group, {{"Letters", Order.Ascending}})
in
  Sort

Power Query solution 12 for Sum Values by Letter Codes, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split1 = Table.FromValue(
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z", " "})(
      Text.Combine(Source[String])
    )
  ), 
  Split2 = Table.SplitColumn(
    Split1, 
    "Value", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Letters", "Quantity"}
  ), 
  Transform = Table.TransformColumns(Split2, {{"Letters", Text.Trim}, {"Quantity", Number.From}}), 
  Result = Table.Sort(
    Table.Group(
      Transform, 
      {"Letters"}, 
      {{"Quantity", each List.Sum([Quantity]), type nullable number}}
    ), 
    "Letters"
  )
in
  Result

Power Query solution 13 for Sum Values by Letter Codes, proposed by Krzysztof Kominiak:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddPos = Table.AddColumn(
    Source, 
    "Pos", 
    each List.Reverse(List.Skip(List.PositionOfAny(Text.ToList([String]), {"A" .. "Z"}, 2)))
  ), 
  SelCol = Table.AddColumn(
    AddPos, 
    "Letters", 
    each List.Accumulate([Pos], [String], (s, c) => Text.Insert(s, c, "||"))
  )[[Letters]], 
  SplitCol = Table.ExpandListColumn(
    Table.TransformColumns(
      SelCol, 
      {
        {
          "Letters", 
          Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Letters"
  ), 
  RemSpaces = Table.ReplaceValue(SplitCol, " ", "", Replacer.ReplaceText, {"Letters"}), 
  AddNumb = Table.AddColumn(
    RemSpaces, 
    "Numb", 
    each Number.From(Text.Select([Letters], {"0" .. "9"}))
  ), 
  SelLetters = Table.TransformColumns(AddNumb, {"Letters", each Text.Remove(_, {"0" .. "9"})}), 
  Result = Table.Sort(
    Table.Group(SelLetters, {"Letters"}, {{"Quantity", each List.Sum([Numb]), type number}}), 
    {"Letters"}
  )
in
  Result

Power Query solution 14 for Sum Values by Letter Codes, proposed by Mahmoud Bani Asadi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SplitColumnbyDelimiter = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {
        {
          "String", 
          Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z", " "}), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "String"
  ), 
  SplitColumnbyCharacterTransition = Table.SplitColumn(
    SplitColumnbyDelimiter, 
    "String", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Letters", "Quantity"}
  ), 
  TrimmedText = Table.TransformColumns(
    SplitColumnbyCharacterTransition, 
    {{"Letters", Text.Trim}, {"Quantity", Number.From}}
  ), 
  GroupedRows = Table.Group(
    TrimmedText, 
    {"Letters"}, 
    {{"Qty", each List.Sum([Quantity]), type nullable text}}
  ), 
  SortedRows = Table.Sort(GroupedRows, {{"Letters", Order.Ascending}})
in
  SortedRows

Power Query solution 15 for Sum Values by Letter Codes, proposed by Anup Kumar:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  RemoveSpace = Table.ReplaceValue(Source, " ", "", Replacer.ReplaceText, {"String"}), 
  TransposedRows = Table.Transpose(RemoveSpace), 
  MAllRows = Table.CombineColumns(
    TransposedRows, 
    {"Column1", "Column2", "Column3", "Column4"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "M"
  ), 
  UppercasedText = Table.TransformColumns(MAllRows, {{"M", Text.Upper, type text}}), 
  SplitbyAlphabet = Table.SplitColumn(
    UppercasedText, 
    "M", 
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)), 
    {
      "M.1", 
      "M.2", 
      "M.3", 
      "M.4", 
      "M.5", 
      "M.6", 
      "M.7", 
      "M.8", 
      "M.9", 
      "M.10", 
      "M.11", 
      "M.12", 
      "M.13", 
      "M.14"
    }
  ), 
  Transposeback = Table.Transpose(SplitbyAlphabet), 
  SplitbyDigit = Table.SplitColumn(
    Transposeback, 
    "Column1", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Letters", "Column1.2"}
  ), 
  ChangedType = Table.TransformColumnTypes(SplitbyDigit, {{"Column1.2", Int64.Type}}), 
  GroupedRows = Table.Group(
    ChangedType, 
    {"Letters"}, 
    {{"Quantity", each List.Sum([Column1.2]), type nullable number}}
  ), 
  SortedRows = Table.Sort(GroupedRows, {{"Letters", Order.Ascending}})
in
  SortedRows

Power Query solution 16 for Sum Values by Letter Codes, proposed by Daniel Madhadha:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Con&tent], 
  #"Split Column by Character Transition" = Table.SplitColumn(
    Source, 
    "String", 
    Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)), 
    {"String.1", "String.2", "String.3", "String.4", "String.5"}
  ), 
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(
    #"Split Column by Character Transition", 
    {}, 
    "Attribute", 
    "Value"
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns", {"Attribute"}), 
  #"Split Column by Character Transition1" = Table.SplitColumn(
    #"Removed Columns", 
    "Value", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Value.1", "Value.2"}
  ), 
  #"Trimmed Text" = Table.TransformColumns(
    Table.TransformColumnTypes(
      #"Split Column by Character Transition1", 
      {{"Value.2", type text}}, 
      "en-US"
    ), 
    {{"Value.1", Text.Trim, type text}, {"Value.2", Text.Trim, type text}}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text", {{"Value.2", type number}}), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"Value.1"}, 
    {{"Count", each List.Sum([Value.2]), type nullable number}}
  ), 
  #"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Value.1", Order.Ascending}})
in
  #"Sorted Rows"

Power Query solution 17 for Sum Values by Letter Codes, proposed by Alejandra Horvath CPA, CGA:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split = Table.TransformRows (Source, each 
let
x = Text.Replace([String], " ","" ),
y = Splitter.SplitTextByCharacterTransition((a) => not List.Contains({"A".."Z"}, a), {"A".."Z"})(x)
in
y),
 ToTbl = Table.FromList(Split, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 Expanded = Table.ExpandListColumn(ToTbl, "Column1"),
 SplitByCharTransition = Table.SplitColumn(Expanded, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Letters", "Quantity"}),
 ChgType = Table.TransformColumnTypes(SplitByCharTransition,{{"Quantity", Int64.Type}}),
 Grouped = Table.Group(ChgType, {"Letters"}, {{"Quantity", each List.Sum([Quantity]), type nullable text}}),
 Sorted = Table.Sort(Grouped,{{"Letters", Order.Ascending}})
in
 Sorted

Cheers 😊

Solving the challenge of Sum Values by Letter Codes with Excel

Excel solution 1 for Sum Values by Letter Codes, proposed by Bo Rydobon 🇹🇭:

=LET(z,CONCAT(A2:A5),a,TEXTSPLIT(z," ",SEQUENCE(10,,0),1),u,UNIQUE(a),SORT(HSTACK(u,MAP(u,LAMBDA(v,SUM(TEXTSPLIT(z,,a,1)*(v=a)))))))

Excel solution 2 for Sum Values by Letter Codes, proposed by محمد حلمي:

=LET(r,CONCAT(A2:A5),v,TEXTSPLIT(r,,VSTACK(" ",ROW(1:10)-1),1),l,SORT(UNIQUE(v)),HSTACK(l,MAP(
l,LAMBDA(a,SUM((a=v)*TEXTSPLIT(r,,v,1))))))

Excel solution 3 for Sum Values by Letter Codes, proposed by 🇰🇷 Taeyong Shin:

=LET(f,LAMBDA(x,TOCOL(REGEXEXTRACT(CONCAT(A2:A5),x,1))),GROUPBY(f("pL"),--f("d+"),SUM,,0))

Excel solution 4 for Sum Values by Letter Codes, proposed by 🇰🇷 Taeyong Shin:

=LET(
 txt, REDUCE(A2:A5, SEQUENCE(10) - 1, LAMBDA(a,c, SUBSTITUTE(a, c & " ", c & ", ") )),
 s, SORT(TEXTSPLIT(ARRAYTOTEXT(txt), , ", ")),
 u, UNIQUE(LEFT(s)),
 SCAN( 0, HSTACK(u, u), LAMBDA(a,c,
 IF(ISNUMBER(a), c, SUM(IFERROR(NUMBERVALUE(s, " ", c), )) )
 ))
)

Excel solution 5 for Sum Values by Letter Codes, proposed by Kris Jaganah:

=LET(a,CONCAT(A2:A5),b,TRIM(TEXTSPLIT(a,,SEQUENCE(10,,0),1)),c,UNIQUE(b),VSTACK({"Letters","Quantity"},SORT(HSTACK(c,MAP(c,LAMBDA(x,SUM((b=x)*TEXTSPLIT(a,,b,1))))))))

Excel solution 6 for Sum Values by Letter Codes, proposed by Alejandro Campos:

=LET(
 _r, CONCAT(A2:A5),
 _c, SUBSTITUTE(_r, " ", ""),
 _t, TEXTSPLIT(_c, , SEQUENCE(10, , 0), 1),
 _n, --TEXTSPLIT(_c, , _t, 1),
 GROUPBY(_t, _n, SUM, 0, 0)
)

Excel solution 7 for Sum Values by Letter Codes, proposed by Oscar Mendez Roca Farell:

=LET(_t, CONCAT(A2:A5),_l, TEXTSPLIT(_t," ", SEQUENCE(10, ,1), 1),_n, TEXTSPLIT(_t, ,_l, 1), UNIQUE( ORDER( HSTACK(_l, ENCOL( MMULT( TOROW(_n)^0,(_l=TOROW(_l))*_n))))))

Excel solution 8 for Sum Values by Letter Codes, proposed by Sunny Baggu:

=LET(
 _str, TEXTJOIN(" ", , A2:A5),
 _alpha, CHAR(SEQUENCE(26, , CODE("A"))),
 _modstr, REDUCE(_str, _alpha, LAMBDA(a, v, SUBSTITUTE(a, v, v & " "))),
 _tbl, SORT(WRAPROWS(TEXTSPLIT(_modstr, , " ", 1), 2)),
 _qty, MAP(
 UNIQUE(DROP(_tbl, , -1)),
 LAMBDA(a, SUM(--FILTER(DROP(_tbl, , 1), DROP(_tbl, , -1) = a)))
 ),
 HSTACK(UNIQUE(DROP(_tbl, , -1)), _qty)
)

Excel solution 9 for Sum Values by Letter Codes, proposed by Sunny Baggu:

=LET(
 _str, TEXTJOIN(" ", , A2:A5),
 _letters, TRIM(TEXTSPLIT(_str, , SEQUENCE(10, , 0), 1)),
 _qty, TEXTSPLIT(_str, , CHAR(SEQUENCE(26, , CODE("A"))), 1) + 0,
 _uletters, SORT(UNIQUE(_letters)),
 HSTACK(_uletters, MAP(_uletters, LAMBDA(a, SUM(IF(_letters = a, _qty, 0)))))
)

Excel solution 10 for Sum Values by Letter Codes, proposed by Pieter de B.:

=LET(r,CONCAT(A2:A5),c,SUBSTITUTE(r," ",""),t,TEXTSPLIT(c,,SEQUENCE(10,,0),1),n,--TEXTSPLIT(c,,t,1),u,UNIQUE(t),m,MMULT(--(TOROW(t)=u),n),SORT(HSTACK(u,m)))

Excel solution 11 for Sum Values by Letter Codes, proposed by JvdV –:

=LET(x,DROP(REDUCE(0,A2:A5,LAMBDA(a,b,VSTACK(a,LET(c,TEXTSPLIT(b,,CHAR(ROW(65:90)),1),HSTACK(TRIM(TEXTSPLIT(b,,c,1)),--c))))),1),y,TAKE(x,,1),REDUCE({"Letters","Quantity"},SORT(UNIQUE(y)),LAMBDA(e,f,VSTACK(e,HSTACK(f,SUM(IF(y=f,DROP(x,,1))))))))

Excel solution 12 for Sum Values by Letter Codes, proposed by Hammed Toheeb:

=LET(a,SUBSTITUTE(CONCAT(A2:A5)," ",""),b,TOCOL(--TEXTSPLIT(a,CHAR(SEQUENCE(26,,65,1)),,TRUE)),c,TOCOL(TEXTSPLIT(a,SEQUENCE(10,,0,1),,TRUE)),VSTACK({"Letters","Quantity"},GROUPBY(c,b,SUM,0,0)))

Excel solution 13 for Sum Values by Letter Codes, proposed by Caroline Blake:

=LET(_a,CONCAT(A2:A5),_b,TEXTSPLIT(_a," ",{1,2,3,4,5,6,7,8,9},TRUE),_c,TEXTSPLIT(_a,,_b,TRUE),_d,UNIQUE(_b),_e,MAP(_d,LAMBDA(_d,SUM(_c*(_d=_b)))),VSTACK(HSTACK("Letters","Quantity"),SORT(HSTACK(_d,_e),,1)))

Solving the challenge of Sum Values by Letter Codes with Python in Excel

Python in Excel solution 1 for Sum Values by Letter Codes, proposed by Alejandro Campos:

import re
df = pd.DataFrame([(c, int(n)) for c, n in re.findall(r'([A-Z])(d+)',
 re.sub(r's+', '', ''.join(xl("A2:A5")[0])))], columns=['Character', 'Number'])
 .groupby('Character', as_index=False)['Number'].sum()
df

Solving the challenge of Sum Values by Letter Codes with R

R solution 1 for Sum Values by Letter Codes, proposed by Krzysztof Nowak:

Answer <- data %>% 
 mutate(Letter = str_extract(Components,"[A-Z]"), # extract letters

Solving the challenge of Sum Values by Letter Codes with Power Query

Solving the challenge of Sum Values by Letter Codes with Excel

Solving the challenge of Sum Values by Letter Codes with Python in Excel

Solving the challenge of Sum Values by Letter Codes with R

Leave a Reply Cancel reply