Home » Lookup Data with a Carriage Return Delimiter

Lookup Data with a Carriage Return Delimiter

Extract the top marks attained Use strictly legacy array functions or power query

📌 Challenge Details and Links
Challenge Number: 3
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Lookup Data with a Carriage Return Delimiter with Power Query

Power Query solution 1 for Lookup Data with a Carriage Return Delimiter, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.TransformColumns(
    Table.DuplicateColumn(Source, "Subjects & Marks", "Top Marks"), 
    {
      "Top Marks", 
      each List.Max(
        List.Transform(
          Splitter.SplitTextByDelimiter("#(lf)")(_), 
          each Number.From(Text.AfterDelimiter(_, ":"))
        )
      )
    }
  )
in
  Ans
Power Query solution 2 for Lookup Data with a Carriage Return Delimiter, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla2"]}[Content], 
  Sol = Table.AddColumn(
    Origen, 
    "Top Mark", 
    each 
      let
        a = Text.Split(Record.ToList(_){1}, "#(lf)"), 
        b = List.RemoveNulls(List.Transform(a, each Number.From(Text.Select(_, {"0" .. "9"})))), 
        c = List.Max(b)
      in
        c
  )[[Top Mark]]
in
  Sol
Power Query solution 3 for Lookup Data with a Carriage Return Delimiter, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SplitByLF = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {
        {
          "Subjects & Marks", 
          Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Subjects & Marks"
  ), 
  SplitbyColon = Table.SelectRows(
    Table.SplitColumn(
      SplitByLF, 
      "Subjects & Marks", 
      Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), 
      {"Subjects", "Marks"}
    ), 
    each [Marks] <> null
  ), 
  ReType = Table.TransformColumnTypes(SplitbyColon, {{"Marks", Int64.Type}}), 
  AddTopMarks = Table.Group(
    ReType, 
    {"Student "}, 
    {{"Top Marks", each List.Max([Marks]), type nullable number}}
  ), 
  Join = Table.Join(Source, "Student ", AddTopMarks, "Student ")
in
  Join
Power Query solution 4 for Lookup Data with a Carriage Return Delimiter, proposed by Bhavya Gupta: 
let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WcknNy8ssVtJRUvBNLMmwUjCxiMmLyXPNS8/JLM6wMjUB8QIyKoszk4utFMzNQdyg1JzM9Mz8PCsFM6C0UqxOtJJrbm4iwgxLA2QzjMxQzLBAN8MYaoZXYiXCCFMjZCNMLFGMMDVDNcIC7IxYAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    {"Student ", "Subjects & Marks"}
  ), 
  Output = Table.AddColumn(
    Source, 
    "Top Marks", 
    each List.Max(
      List.Transform(
        Text.Split([#"Subjects & Marks"], "#(lf)#(lf)"), 
        (t) => Number.From(Text.AfterDelimiter(t, ":"))
      )
    )
  )
in
  Output
Power Query solution 5 for Lookup Data with a Carriage Return Delimiter, proposed by JvdV –: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.AddColumn(
    Source, 
    "Top Marks", 
    each List.Accumulate(
      {1 .. 100}, 
      0, 
      (s, c) => if Text.Contains([#"Subjects & Marks"], Number.ToText(c)) then c else s
    )
  )
in
  Ans

Solving the challenge of Lookup Data with a Carriage Return Delimiter with Excel

Excel solution 1 for Lookup Data with a Carriage Return Delimiter, proposed by Rick Rothstein: 
=MAX(
   0+SUBSTITUTE(
       LEFT(
           TRIM(
               MID(
                   SUBSTITUTE(
                       ":000"&C3,
                       ":",
                       REPT(
                           " ",
                           200)),
                   ROW(
                       A$1:A$5)*200,
                   200)),
           3),
       CHAR(
           10),
       ""))
Excel solution 2 for Lookup Data with a Carriage Return Delimiter, proposed by Rick Rothstein: 
=MAX(
   ISNUMBER(
       FIND(
           ROW(
               $1:$100),
           C3))*ROW(
               $1:$100))
Excel solution 3 for Lookup Data with a Carriage Return Delimiter, proposed by محمد حلمي: 
=MAX(
   IFERROR(
       --RIGHT(
           
           TRIM(
               MID(
                   SUBSTITUTE(
                       C2,
                       CHAR(
                           10),
                       
                       REPT(
                           " ",
                           99)),
                   ROW(
                       $1:$99)*99-98,
                   99)),
           2),
       ))
Excel solution 4 for Lookup Data with a Carriage Return Delimiter, proposed by 🇰🇷 Taeyong Shin: 
=MAX(
   FILTERXML(
       "<t><s>" & SUBSTITUTE(
           SUBSTITUTE(
               C3,
                CHAR(
                    10),
                ":"),
            ":",
            "</s><s>") & "</s></t>",
        "//s[.*0=0]"))
Excel solution 5 for Lookup Data with a Carriage Return Delimiter, proposed by 🇰🇷 Taeyong Shin: 
=MAP(C3:C5,
   LAMBDA(x,
   MAX(--(0&TEXT(
       TEXTSPLIT(
           x,
           CHAR(
               10),
           ":",
           1,
           ,
           0),
       "0;;;")))))
Excel solution 6 for Lookup Data with a Carriage Return Delimiter, proposed by Kris Jaganah: 
=MAP(
   C3:C5,
   LAMBDA(
       x,
       MAX(
           --LEFT(
               TRIM(
                   TEXTAFTER(
                       x,
                       ":",
                       {1,
                       2,
                       3,
                       4})),
               2))))
Excel solution 7 for Lookup Data with a Carriage Return Delimiter, proposed by Oscar Mendez Roca Farell: 
=AGGREGATE(14,
   4,
   --MID(SUBSTITUTE(
       TRIM(
           SUBSTITUTE(
               SUBSTITUTE(
                   C3,
                   ":",
                   " "),
               CHAR(
                   10),
               " ")),
       " ",
       REPT(
           " ",
           50)),
   50*(2*ROW(
       $1:$4)-1),
   50),
   1)
Excel solution 8 for Lookup Data with a Carriage Return Delimiter, proposed by Ankur Sharma: 
=BYROW(
   C3:C5,
    LAMBDA(
       a,
        MAX(
            IF(
                ISERROR(
                    SEARCH(
                        SEQUENCE(
                            99),
                         a)),
                 0,
                 1) * SEQUENCE(
                            99))))
Excel solution 9 for Lookup Data with a Carriage Return Delimiter, proposed by Rahim Zulfiqar Ali: 
=MAX(
   --TEXTSPLIT(
       TEXTJOIN(
           ", ",
           ,
           TEXTSPLIT(
               B2,
               TEXTSPLIT(
                   B2,
                   SEQUENCE(
                       10,
                       ,
                       0),
                   ,
                   1),
               ,
               1)),
       ", "))

Leave a Reply