Home » Match Output to Picture

Match Output to Picture

Generate the output as shown in picture.

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

Solving the challenge of Match Output to Picture with Power Query

Power Query solution 1 for Match Output to Picture, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Data", "Data.2"}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Split Column by Delimiter", 
    {"Data"}, 
    {{"Answer Expected", each Text.Combine(List.Distinct([Data.2]), ", "), type nullable text}}
  )
in
  #"Grouped Rows"
Power Query solution 2 for Match Output to Picture, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SplitDelimiter = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Data", "Data.2"}
  ), 
  ChangedType = Table.TransformColumnTypes(SplitDelimiter, {{"Data.2", Int64.Type}}), 
  Result = Table.Group(
    ChangedType, 
    {"Data"}, 
    {
      {
        "Answer Expected", 
        each Text.Combine(
          List.Transform(List.Distinct(List.Sort([Data.2], Order.Descending)), each Text.From(_)), 
          ", "
        )
      }
    }
  )
in
  Result
Power Query solution 3 for Match Output to Picture, proposed by Luan Rodrigues: 
let
 Fonte = Data,
 a = Table.SplitColumn(Fonte, "Data", Splitter.SplitTextByDelimiter("-"), {"Data.1", "Data.2"}),
 Result = Table.Group(a, {"Data.1"}, {
 {"Contagem", each 
 [
 a = List.Distinct(_[Data.2]),
 b = List.Transform(a,Number.From),
 c = List.Sort(b,Order.Ascending),
 d = List.Transform(c,Text.From),
 e = Text.Combine(d,",")
 ][e]
 }})
in
 Result
Power Query solution 4 for Match Output to Picture, proposed by Brian Julius: 
let
  Source = Table.TransformColumnTypes(
    Table.SplitColumn(
      LettNumRaw, 
      "Data", 
      Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
      {"Letter", "Number"}
    ), 
    {"Number", Int64.Type}
  ), 
  Group = Table.Group(
    Source, 
    {"Letter"}, 
    {{"All", each _, type table [Letter = nullable text, Number = nullable text]}}
  ), 
  Sort = Table.AddColumn(
    Group, 
    "Numbers", 
    each List.Sort(List.Distinct([All][Number]), Order.Ascending)
  ), 
  Extract = Table.RemoveColumns(
    Table.TransformColumns(
      Sort, 
      {"Numbers", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
    ), 
    "All"
  )
in
  Extract
Power Query solution 5 for Match Output to Picture, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Data", "Expected Output"}
  ), 
  ExpectedOutput = Table.Group(
    Split, 
    {"Data"}, 
    {
      {
        "Expected Output", 
        each Text.Combine(
          List.Transform(
            List.Sort(List.Distinct([Expected Output]), {each Number.From(_)}), 
            each Text.From(_)
          ), 
          ", "
        )
      }
    }
  )
in
  ExpectedOutput
Power Query solution 6 for Match Output to Picture, proposed by Jaroslaw Kujawa: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table11"]}[Content], 
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Data.1", "Data.2"}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Split Column by Delimiter", 
    {"Data.1"}, 
    {
      {
        "Count", 
        each Lines.ToText(List.RemoveNulls(List.Distinct(Table.Column(_, "Data.2"))), ", "), 
        type table [Data.1 = nullable text, Data.2 = nullable text]
      }
    }
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows", 
    "Custom", 
    each 
      if Text.EndsWith([Count], ", ") then
        Text.Start([Count], Text.Length([Count]) - 2)
      else
        [Count]
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Count"})
in
  #"Removed Columns"
Power Query solution 7 for Match Output to Picture, proposed by Matthias Friedmann: 
let
 Source = Excel.CurrentWorkbook(){[Name = "Grouping"]}[Content], 
 #"Split Column by Delimiter" = Table.SplitColumn(
 Source, 
 "Data", 
 Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
 {"Group", "Data"}
 ), 
 #"Grouped Rows" = Table.Group(
 #"Split Column by Delimiter", 
 {"Group"}, 
 {{"Result", each 
 Text.Combine(
 List.Sort(
 List.Distinct([Data]),
 each Number.From(_)
 ),
 ", ")
 }}
 )
in
 #"Grouped Rows"


Nested step by step:
let
 Source = Excel.CurrentWorkbook(){[Name = "Grouping"]}[Content], 
 #"Split Column by Delimiter" = Table.SplitColumn(
 Source, 
 "Data", 
 Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
 {"Group", "Data"}
 ), 
 #"Grouped Rows" = Table.Group(
 #"Split Column by Delimiter", 
 {"Group"}, 
 {{"Result", each 
 Text.Combine(
 List.Transform(
 List.Sort(
 List.Transform(
 List.Distinct([Data]),
 each Number.From(_)
 )
 ),
 each Text.From(_)
 ),
 ", ")
 }}
 )
in
 #"Grouped Rows"
Power Query solution 8 for Match Output to Picture, proposed by Antriksh Sharma: 
let
  Source = Raw[Data], 
  Split = List.Transform(
    Source, 
    each Table.FromRows(
      {
        {
          Text.Select(_, {"a" .. "z", "A" .. "Z"}), 
          Number.From(Text.Select(_, {"0" .. "9"})), 
          Text.Select(_, {"0" .. "9"})
        }
      }, 
      type table [Char = text, Num = Int64.Type, NumText = text]
    )
  ), 
  ToTable = Table.Combine(Split), 
  SortedRows = Table.Sort(ToTable, {{"Char", Order.Ascending}, {"Num", Order.Ascending}}), 
  GroupedRows = Table.Group(
    SortedRows, 
    {"Char"}, 
    {{"Count", each Text.Combine(List.Distinct(_[NumText]), ", "), type text}}
  )
in
  GroupedRows
Power Query solution 9 for Match Output to Picture, proposed by Victor Wang: 
let
 Source = Table.FromColumns({{"A","B","C","D","E"},{"1,4,9","2,33","3,432,9",null,"12"}})
in
 Source

But, if you wanted to actually use the data...here's a pretty standard solution:
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Split = Table.SplitColumn(Source, "Data", Splitter.SplitTextByDelimiter("-"), {"Answer", "Data.2"}),
 Group = Table.Group(Split, {"Answer"}, {{"Expected", each Text.Combine(List.Distinct(List.Sort(_[Data.2])),",")}})
in
 Group
Power Query solution 10 for Match Output to Picture, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.ToRecords(Source), 
  b = List.Transform(a, each Text.Split([Data], "-")), 
  c = List.Zip(b), 
  d = Table.FromColumns(c), 
  e = Table.Pivot(
    d, 
    List.Distinct(d[Column1]), 
    "Column1", 
    "Column2", 
    each Text.Combine(List.Distinct(List.Sort(_, each Number.From(_))), ",")
  ), 
  f = Table.DemoteHeaders(e), 
  g = Table.Transpose(f)
in
  g
Power Query solution 11 for Match Output to Picture, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source, 
    "Data", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Data.1", "Data.2"}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Split Column by Delimiter", 
    {{"Data.1", type text}, {"Data.2", Int64.Type}}
  ), 
  #"Removed Duplicates" = Table.Distinct(#"Changed Type"), 
  #"Sorted Rows" = Table.Sort(
    #"Removed Duplicates", 
    {{"Data.1", Order.Ascending}, {"Data.2", Order.Ascending}}
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows", {{"Data.2", type text}}), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type1", 
    {"Data.1"}, 
    {{"Count", each Text.Combine([Data.2], ","), type nullable text}}
  )
in
  #"Grouped Rows"

Solving the challenge of Match Output to Picture with Excel

Excel solution 1 for Match Output to Picture, proposed by Rick Rothstein: 
=LET(r,MID(REDUCE("",UNIQUE(SORT(A2:A14)),LAMBDA(a,x,LET(m,MID(x,3,99),l,LEFT(x),IF(LEFT(a)=l,a&","&m,l&MID(a,2,99)&"|"&l&"/"&m)))),3,99),t,TEXTSPLIT(r,,"|"),HSTACK(LEFT(t),MID(t,3,99)))
Excel solution 2 for Match Output to Picture, proposed by John V.: 
=LET(
    d,
    A2:A14,
    l,
    LEFT(
        d
    ),
    u,
    UNIQUE(
        l
    ),
    HSTACK(
        u,
        MAP(
            u,
            LAMBDA(
                x,
                IFERROR(
                    TEXTJOIN(
                        ", ",
                        ,
                        SORT(
                            UNIQUE(
                                IF(
                                    l=x,
                                    --MID(
                                        d,
                                        3,
                                        99
                                    ),
                                    ""
                                )
                            )
                        )
                    ),
                    ""
                )
            )
        )
    )
)
Excel solution 3 for Match Output to Picture, proposed by محمد حلمي: 
=LET(
a,A2:A14,
r,TEXTSPLIT(a,"-"),
v,UNIQUE(r),
HSTACK(v,MAP(v,LAMBDA(x,TEXTJOIN(", ",, SORT(
UNIQUE(IF(x=r,IFNA(TEXTAFTER(a,"-")+0,""),""))))))))
Excel solution 4 for Match Output to Picture, proposed by محمد حلمي: 
=LET(
a,A2:A14,
v,UNIQUE(TEXTSPLIT(a,"-")),
HSTACK(v,MAP(v,LAMBDA(x,TEXTJOIN(", ",, SORT(
UNIQUE(IF(x=TEXTSPLIT(a,"-"),
IFNA(TEXTAFTER(a,"-")+0,""),""))))))))
Excel solution 5 for Match Output to Picture, proposed by 🇰🇷 Taeyong Shin: 
=LET(d,A2:A14,L,LAMBDA(f,f(d,"-",,,1)),TEXT(GROUPBY(L(TEXTBEFORE),--(0&L(TEXTAFTER)),LAMBDA(x,ARRAYTOTEXT(SORT(UNIQUE(x)))),,0),"0;;"))
Excel solution 6 for Match Output to Picture, proposed by 🇰🇷 Taeyong Shin: 
=LET(
    
     Data,
     A2:A14,
    
     ua,
     UNIQUE(
         LEFT(
             Data
         )
     ),
    
     str,
     MAP(
         ua,
          LAMBDA(
              m,
              
               ARRAYTOTEXT(
                    SORT(
                        -UNIQUE(
                            TOCOL(
                                TEXTAFTER(
                                    Data & 0,
                                     m
                                ),
                                 2
                            )
                        )
                    )/10 
               )
               
          )
     ),
    
     HSTACK(
         ua,
          TEXT(
              str,
               "0;;"
          )
     )
    
)
Excel solution 7 for Match Output to Picture, proposed by Kris Jaganah: 
=LET(
    a,
    A2:A14,
    b,
    TEXTSPLIT(
        a,
        "-"
    ),
    c,
    IFERROR(
        TEXTAFTER(
            a,
            "-"
        ),
        ""
    ),
    d,
    UNIQUE(
        b
    ),
    e,
    HSTACK(
        d,
        BYROW(
            d,
            LAMBDA(
                x,
                TEXTJOIN(
                    ",",
                    TRUE,
                    SORT(
                        IFERROR(
                            UNIQUE(
                                FILTER(
                                    c,
                                    b=x
                                )
                            )/1,
                            ""
                        ),
                        1,
                        1
                    )
                )
            )
        )
    ),
    e
)
Excel solution 8 for Match Output to Picture, proposed by Julian Poeltl: 
=LET(D,A2:A14,B,TEXTBEFORE(D,"-",,,,D),A,TEXTAFTER(D,"-"),U,UNIQUE(B),HSTACK(U,IFERROR(MAP(U,LAMBDA(C,TEXTJOIN(", ",,SORT(--UNIQUE(FILTER(A,B=C)))))),"")))
Excel solution 9 for Match Output to Picture, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _d, A2:A14,
 _fc, LEFT(_d),
 _ufc, UNIQUE(_fc),
 _e, LAMBDA(a,
 ARRAYTOTEXT(
 SORT(
 UNIQUE(
 IFERROR(--FILTER(REPLACE(_d, 1, 2, ""), _fc = a), "")
 )
 )
 )
 ),
 _r, HSTACK(_ufc, MAP(_ufc, _e)),
 _r
)
Excel solution 10 for Match Output to Picture, proposed by Timothée BLIOT: 
=LET(Characters, TEXTSPLIT(TEXTJOIN("/",1,A2:A14),"-","/"), 
IFERROR(UNIQUE(HSTACK(INDEX(Characters,,1), MAP(INDEX(Characters,,1), LAMBDA(a, TEXTJOIN(", ",1,UNIQUE(FILTER(INDEX(Characters,,2),INDEX(Characters,,1)=a))) )) )),""))
Excel solution 11 for Match Output to Picture, proposed by Bhavya Gupta: 
=LET(Data,UNIQUE(Table1[Data]),TB,TEXTBEFORE(Data,"-",,,1),U,UNIQUE(TB),HSTACK(U,MAP(U,LAMBDA(x,ARRAYTOTEXT(SORT(FILTER(IFERROR(TEXTAFTER(Data,"-",,,1)*1,""),TEXTBEFORE(Data,"-",,,1)=x)))))))
Excel solution 12 for Match Output to Picture, proposed by Jardiel Euflázio: 
=LET(b,A2:A14,c,SORT(UNIQUE(TEXTBEFORE(b&"-","-"))),HSTACK(c,MAP(c,LAMBDA(a,TEXTJOIN(", ",,UNIQUE(FILTER(SUBSTITUTE(TEXTAFTER(b&"-","-"),"-",""),ISNUMBER(FIND(a,b)))))))))
Excel solution 13 for Match Output to Picture, proposed by Jardiel Euflázio: 
=LET(
    b,
    A2:A14,
    c,
    LEFT(
        b
    ),
    d,
    SORT(
        UNIQUE(
            c
        )
    ),
    CHOOSE(
        {12},
        d,
        BYROW(
            d,
            LAMBDA(
                a,
                TEXTJ&OIN(
                    ", ",
                    ,
                    UNIQUE(
                        TEXTAFTER(
                            FILTER(
                                b,
                                c=a
                            ),
                            "-",
                            ,
                            ,
                            ,
                            ""
                        )
                    )
                )
            )
        )
    )
)
Excel solution 14 for Match Output to Picture, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=LET(a,A2:A14,
b,UNIQUE(TEXTBEFORE(a,"-",,,1)),
HSTACK(b,
MAP(b,
LAMBDA(x,
TEXTJOIN(", ",,IFERROR(SORT(0+TEXTAFTER(UNIQUE(FILTER(a,LEFT(a)=x)),"-",,,1)),""))))))

the second, a little longer, but would work in any scenario, i think
=LET(b,A2:A14,
a,UNIQUE(TEXTBEFORE(b,"-",,,1)),
HSTACK(a,
MAP(a,
LAMBDA(x,
TEXTJOIN(", ",1,IFERROR(SORT(0+UNIQUE(TEXTAFTER(FILTER(b,ISNUMBER(SEARCH(x,b))),"-",,,1))),""))))))
Excel solution 15 for Match Output to Picture, proposed by El Badlis Mohd Marzudin: 
=LET(
    
    _data,
    UNIQUE(
        A2:A14
    ),
    
    _apb,
    LEFT(
        _data
    ),
    
    _num,
    IFNA(
        TEXTAFTER(
            _data,
            "-"
        )+0,
        ""
    ),
    
    
    HSTACK(
        UNIQUE(
            _apb
        ),
        
        
        BYROW(
            UNIQUE(
            _apb
        ),
            
            LAMBDA(
                a,
                TEXTJOIN(
                    ", ",
                    1,
                    SORT(
                        FILTER(
                            _num,
                            _apb=a
                        ),
                        ,
                        1
                    )
                )
            )
        )
    )
)
Excel solution 16 for Match Output to Picture, proposed by RIJESH T.: 
=LET(
    d,
    A2:A14,
    b,
    TEXTBEFORE(
        d,
        "-",
        ,
        ,
        1
    ),
    a,
    TEXTAFTER(
        d,
        "-"
    ),
    u,
    UNIQUE(
        b
    ),
    HSTACK(
        u,
        MAP(
            u,
            LAMBDA(
                x,
                ARRAYTOTEXT(
                    SORT(
                        UNIQUE(
                            IFERROR(
                                FILTER(
                                    a,
                                    b=x,
                                    " "
                                )+0,
                                ""
                            )
                        )
                    )
                )
            )
        )
    )
)
Excel solution 17 for Match Output to Picture, proposed by Fábio Gatti: 
=LAMBDA(Arr,DelimiterSplit,DelimiterResult,
 LET(
 xA,IFERROR(TEXTBEFORE(Arr,DelimiterSplit),Arr),
 xB,TEXTAFTER(Arr,DelimiterSplit),
 xUniqueA,SORT(UNIQUE(xA)),
 fxFilter,LAMBDA(Value,IFERROR(TEXTJOIN(DelimiterResult,1,SORT(--UNIQUE(FILTER(xB,xA=Value)))),"")),
 xResultB,BYROW(xUniqueA,fxFilter),
 Result,HSTACK(xUniqueA,xResultB),

 Result
 )
)(A2:A14,"-",", ")

Solving the challenge of Match Output to Picture with SQL

SQL solution 1 for Match Output to Picture, proposed by Zoran Milokanović: 
SELECT
 F.TEXT AS ANSWER
,REPLACE(LISTAGG(F.DIGIT), ',', ', ') AS EXPECTED
FROM
(
 SELECT DISTINCT
 D.DATA
 ,REGEXP_REPLACE(D.DATA, '[[:digit:]|-]+') AS TEXT
 ,DECODE(REGEXP_REPLACE(D.DATA, '[^[:digit:]]+'), '', NULL,
 TO_NUMBER(REGEXP_REPLACE(D.DATA, '[^[:digit:]]+')) 
 ) AS DIGIT
 FROM DATA D
 ORDER BY
 2, 3
) F
GROUP BY
 F.TEXT
;

Leave a Reply