Home » Subjects Taught Exclusively

Subjects Taught Exclusively

Column A lists down subjects taught by Female and Male Teachers. Find the subjects which Female teachers teach but male teachers don’t teach and vice versa.

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

Solving the challenge of Subjects Taught Exclusively with Power Query

Power Query solution 1 for Subjects Taught Exclusively, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Grouped = Table.Group(
    Source, 
    {"Gender"}, 
    {{"A", each List.Distinct(List.Sort(Text.Split(Text.Combine([Subjects], ", "), ", ")))}}
  ), 
  Miss = Table.RemoveColumns(
    Table.AddColumn(
      Grouped, 
      "Missing Subjects", 
      each Text.Combine(
        List.Difference(
          [A], 
          List.Combine(Table.SelectRows(Grouped, (x) => x[Gender] <> [Gender])[A])
        ), 
        ", "
      )
    ), 
    "A"
  )
in
  Miss
Power Query solution 2 for Subjects Taught Exclusively, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Split = Table.Distinct (Table.ExpandListColumn(Table.TransformColumns(Source, {{"Subjects", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}), "Subjects")[[Subjects],[Gender]]),
 Males = Table.SelectRows(Split, each ([Gender] = "Male")),
 Females = Table.SelectRows(Split, each ([Gender] = "Female")),
 Merged = Table.NestedJoin(Males, {"Subjects"}, Females, {"Subjects"}, "Females", JoinKind.FullOuter),
 Expanded = Table.ExpandTableColumn(Merged, "Females", {"Subjects", "Gender"}, {"Subjects.1", "Gender.1"}),
 AddedMaleCol = Table.AddColumn(Expanded, "Male", each if [Subjects] = null then [Subjects.1] else null),
 AddedFemaleCol = Table.AddColumn(AddedMaleCol, "Female", each if [Subjects.1] = null then [Subjects] else null),


Continue.....
Power Query solution 3 for Subjects Taught Exclusively, proposed by Luan Rodrigues: 
Just an observation!
List.Difference(),
List.RemoveMatchingItems(),
not List.Contains()
List.RemoveItems()
Power Query solution 4 for Subjects Taught Exclusively, proposed by Luan Rodrigues: 
let
  Fonte = Data, 
  a = Table.Group(
    Fonte, 
    {"Gender"}, 
    {
      {
        "Contagem", 
        each List.Distinct(Text.ToList(Text.Select(Lines.ToText(_[Subjects]), {"A" .. "Z"})))
      }
    }
  ), 
  b = Table.PromoteHeaders(Table.Transpose(a), [PromoteAllScalars = true]), 
  c = Table.AddColumn(
    b, 
    "Personalizar", 
    each [
      a      = List.Difference([Female], [Male]), 
      b      = List.Difference([Male], [Female]), 
      Female = Text.Combine(List.Transform(a, Text.From), ", "), 
      Male   = Text.Combine(List.Transform(b, Text.From), ", ")
    ][[Female], [Male]]
  )[[Personalizar]], 
  d = Table.ExpandRecordColumn(c, "Personalizar", {"Female", "Male"}, {"Female", "Male"}), 
  Result = Table.UnpivotOtherColumns(d, {}, "Gender", "Missing Subjects")
in
  Result
Power Query solution 5 for Subjects Taught Exclusively, proposed by Brian Julius: 
let
 Source = TeachingRaw,
 SplitToRows = Table.RemoveColumns( Table.ExpandListColumn(Table.TransformColumns(Source, {{"Subjects", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Subjects"), "Names"),
 Trim = Table.TransformColumns(SplitToRows,{{"Subjects", Text.Trim, type text}}),
 Female = Table.Distinct( Table.SelectRows( Trim, each [Gender] = "Female")),
 Male = Table.Distinct( Table.SelectRows( Trim, each [Gender] = "Male" )),
 FemaleOnly = List.RemoveMatchingItems( Female[Subjects], Male[Subjects]),
 MaleOnly = List.RemoveMatchingItems( Male[Subjects], Female[Subjects])
in
 hashtag#table(
 { "Gender", "Missing Subjects" }, 
 {
 { "Female", Text.Combine( MaleOnly, ", ") }, 
 { "Male", Text.Combine( FemaleOnly, ", ") } 
 }
)
Power Query solution 6 for Subjects Taught Exclusively, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Split = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {{"Subjects", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}
    ), 
    "Subjects"
  ), 
  ExpectedOutput = Table.FromRows(
    List.Transform(
      List.Distinct(Split[Gender]), 
      (a) => {a}
        & {
          Text.Combine(
            List.Difference(
              List.Distinct(Split[Subjects]), 
              List.Transform(
                List.Select(Table.ToRecords(Split), each [Gender] <> a), 
                each _[Subjects]
              )
            ), 
            ", "
          )
        }
    ), 
    {"Gender", "Missing Subjects"}
  )
in
  ExpectedOutput
Power Query solution 7 for Subjects Taught Exclusively, proposed by Bhavya Gupta: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Subjects", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}), "Subjects"),
 AllSubjects = List.Distinct(Split [Subjects]),
 Final = Table.Group(Split, {"Gender"}, {{"All", each Text.Combine(List.Difference(AllSubjects,List.Distinct([Subjects])),", ")}})
in
 Final

P.S. - Output is as per what was asked in statement. It is opposite of what is displayed as Expected Output.
Power Query solution 9 for Subjects Taught Exclusively, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Subjects", type text}, {"Names", type text}, {"Gender", type text}}),
 #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Subjects", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Subjects"),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Subjects", type text}}),
 #"Removed Duplicates" = Table.Distinct(#"Changed Type1", {"Subjects"}),
 #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Subjects", Order.Ascending}}),
 #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
 Subject = Table.SelectColumns(#"Added Index",{"Index", "Subjects"}),
 Back = #"Changed Type1",
 #"Filtered Rows" = Table.SelectRows(Back, each ([Gender] = "Female")),
 #"Removed Duplicates1" = Table.Distinct(#"Filtered Rows", {"Subjects"}),
 FemaleSub = Table.SelectColumns(#"Removed Duplicates1",{"Subjects", "Gender"}),
 Custom1 = Back,
 #"Filtered Rows1" = Table.SelectRows(Custom1, each ([Gender] = "Male")),
 

 #"Sorted Rows1" = Table.Sort(#"Filtered Rows1",{{"Subjects", Order.Ascending}}),
 #"Removed Duplicates2" = Table.Distinct(#"Sorted Rows1", {"Subjects"}),
 MaleSub = Table.SelectColumns(#"Removed Duplicates2",{"Subjects", "Gender"}),
 Back2 = Subject,
 Custom2 = Table.NestedJoin(Back2,{"Subjects"},FemaleSub,{"Subjects"},"Back2",JoinKind.LeftOuter),
 Tbl2 = Table.ExpandTableColumn(Custom2, "Back2", {"Gender"}, {"Back2.Gender"}),
 Custom3 = Table.NestedJoin(Tbl2,{"Subjects"},MaleSub,{"Subjects"},"Tbl2",JoinKind.LeftOuter),
 #"Expanded Tbl2" = Table.ExpandTableColumn(Custom3, "Tbl2", {"Gender"}, {"Gender"}),
 #"Renamed Columns" = Table.RenameColumns(#"Expanded Tbl2",{{"Back2.Gender", "Female"}, {"Gender", "Male"}}),
 #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Female] = null then [Male] else if [Male] = null then [Female] else "Both"),
 #"Filtered Rows2" = Table.SelectRows(#"Added Conditional Column", each ([Custom] <> "Both")),
 #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows2",{{"Custom", "Gender"}}),
 #"Grouped Rows" = Table.Group(#"Renamed Columns1", {"Gender"}, {{"Answer Expected", each Text.Combine([Subjects],", "), type text}})
in
 #"Grouped Rows"
Power Query solution 10 for Subjects Taught Exclusively, proposed by Matthias Friedmann: 
let
 Source = Excel.CurrentWorkbook(){[Name = "Subjects"]}[Content], 
 SplitIntoRows = Table.ExpandListColumn(
 Table.TransformColumns(
 Source, 
 {"Subjects", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}
 ), 
 "Subjects"
 ), 
 All = List.Distinct(SplitIntoRows[Subjects]), 
 Female = Text.Combine(
 List.Difference(
 All, 
 Table.SelectRows(SplitIntoRows, each ([Gender] = "Male"))[Subjects]
 ), 
 ", "
 ), 
 Male = Text.Combine(
 List.Difference(
 All, 
 Table.SelectRows(SplitIntoRows, each ([Gender] = "Female"))[Subjects]
 ), 
 ", "
 ), 
 TableFromColumns = Table.FromColumns(
 {{"Female", "Male"}} & {{Female, Male}}, 
 {"Gender", "Missing Subjects"}
 )
in
 TableFromColumns

Saw Luan's post on alternatives => adapted my "no List.Contains" solution. [Not enough space for both solutions in one comment.]
Power Query solution 11 for Subjects Taught Exclusively, proposed by Antriksh Sharma: 
let
 Source = Raw,
 SplitSubjects = 
 Table.ExpandListColumn ( 
 Table.AddColumn ( Raw, "Subject", each Text.Split ( [Subjects], ", " ) ),
 "Subject"
 )[[Gender], [Subject]],
 RemovedDuplicates = Table.Distinct(SplitSubjects),
 NewTable = hashtag#table ( type table [ Gender = text, Missing Subjects = text ], {} ),
 Result = 
 List.Accumulate (
 List.Distinct ( Source[Gender] ),
 NewTable,
 ( State, Current ) =>
 let
 CurrentGenderRows = 
 Table.SelectRows ( RemovedDuplicates, each [Gender] = Current )[Subject],
 OtherGenderRows = 
 Table.SelectRows ( RemovedDuplicates, each [Gender] <> Current )[Subject],
 Difference = List.Difference ( CurrentGenderRows, OtherGenderRows ),
 Result = 
 Table.InsertRows ( 
 State,
 Table.RowCount ( State ),
 {
 [Gender = Current, Missing Subjects = Text.Combine ( Difference, ", " )]
 }
 )
 in 
 Result
 )
in
 Result
Power Query solution 12 for Subjects Taught Exclusively, proposed by Venkata Rajesh: 
let
  Source = Data, 
  Group = Table.Group(
    Source, 
    {"Gender"}, 
    {{"Subjects", each Text.Combine([Subjects], ", "), type nullable text}}
  ), 
  Output = Table.AddColumn(
    Group, 
    "Missing Subjects", 
    each 
      let
        _list1 = Text.ToList(Group{[Gender = "Male"]}[Subjects]), 
        _list2 = Text.ToList(Group{[Gender = "Female"]}[Subjects])
      in
        if [Gender] = "Female" then
          Text.Combine(List.Distinct(List.RemoveMatchingItems(_list2, _list1)), ", ")
        else
          Text.Combine(List.Distinct(List.RemoveMatchingItems(_list1, _list2)), ", "), 
    Text.Type
  )[[Gender], [Missing Subjects]]
in
  Output
Power Query solution 13 for Subjects Taught Exclusively, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TransCol = Table.TransformColumns(Source, {"Subjects", each Text.Split(_, ", ")}), 
  StartTab = Table.Distinct(Table.SelectColumns(TransCol, "Gender")), 
  AddFemList = Table.AddColumn(
    StartTab, 
    "Ls1", 
    each List.Union(Table.SelectRows(TransCol, (x) => [Gender] = x[Gender])[Subjects])
  ), 
  AddMalList = Table.AddColumn(
    AddFemList, 
    "Ls2", 
    each List.Union(Table.SelectRows(TransCol, (x) => [Gender] <> x[Gender])[Subjects])
  ), 
  AddDiffLists = Table.AddColumn(AddMalList, "Missing Subjects", each List.Difference([Ls1], [Ls2])), 
  Result = Table.TransformColumns(
    Table.SelectColumns(AddDiffLists, {"Gender", "Missing Subjects"}), 
    {"Missing Subjects", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
  )
in
  Result
Power Query solution 14 for Subjects Taught Exclusively, proposed by Jan Willem Van Holst: 
let
 
 Source = Your Data,
 #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Subjects", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Subjects"),
 AllList = List.Distinct(#"Split Column by Delimiter"[Subjects]),
 FemaleList = List.Distinct(Table.SelectRows(#"Split Column by Delimiter", each ([Gender] = "Female"))[Subjects]),
 MaleList = List.Distinct(Table.SelectRows(#"Split Column by Delimiter", each ([Gender] = "Male"))[Subjects]),
 DiffAllFemale = List.Difference(AllList,FemaleList), // solution row "Male"
 DiffAllMale = List.Difference(AllList,MaleList), // solution row "Female"
 Result = Table.FromColumns(
 {{"Female", "Male"},
 {Text.Combine(DiffAllMale, ", "), Text.Combine(DiffAllFemale, ", ")}},
 {"Gender", "Missing Subjects"}
 )
in
Result
Power Query solution 15 for Subjects Taught Exclusively, proposed by Thomas DUCROQUETZ: 
let
  RawData = YourData, 
  allSubjects = List.Distinct(Text.Split(Text.Combine(RawData[Subjects], ", "), ", ")), 
  groupedByGender = Table.Group(
    RawData, 
    {"Gender"}, 
    {
      {
        "Missing Subjects", 
        each 
          let
            distinctSubjects  = List.Distinct(Text.Split(Text.Combine(_[Subjects], ", "), ", ")), 
            subjectsNotTaught = List.RemoveMatchingItems(allSubjects, distinctSubjects)
          in
            Text.Combine(subjectsNotTaught, ", "), 
        type text
      }
    }
  )
in
  groupedByGender

Solving the challenge of Subjects Taught Exclusively with Excel

Excel solution 1 for Subjects Taught Exclusively, proposed by Bo Rydobon 🇹🇭: 
=LET(
    s,
    A2:A11,
    g,
    C2:C11,
    j,
    LAMBDA(
        a,
        SORT(
            UNIQUE(
                TEXTSPLIT(
                    CONCAT(
                        a&", "
                    ),
                    ,
                    ", ",
                    1
                )
            )
        )
    ),
    a,
    j(
        s
    ),
    
    u,
    UNIQUE(
        g
    ),
    HSTACK(
        u,
        MAP(
            u,
            LAMBDA(
                x,
                ARRAYTOTEXT(
                    FILTER(
                        a,
                        ISNA(
                            XMATCH(
                                a,
                                j(
                                    REPT(
                                        s,
                                        g<>x
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)
Excel solution 2 for Subjects Taught Exclusively, proposed by John V.: 
=LET(
    f,
    "Female",
    m,
    "Male",
    h,
    LAMBDA(
        g,
        UNIQUE(
            TEXTSPLIT(
                CONCAT(
                    FILTER(
                        A2:A11,
                        C2:C11=g
                    )&", "
                ),
                ,
          &      ", ",
                1
            )
        )
    ),
    i,
    LAMBDA(
        x,
        y,
        ARRAYTOTEXT(
            FILTER(
                x,
                ISNA(
                    XMATCH(
                        x,
                        y
                    )
                )
            )
        )
    ),
    HSTACK(
        VSTACK(
            C1,
            f,
            m
        ),
        VSTACK(
            "Missing "&A1,
            i(
                h(
                    f
                ),
                h(
                    m
                )
            ),
            i(
                h(
                    m
                ),
                h(
                    f
                )
            )
        )
    )
)
Excel solution 3 for Subjects Taught Exclusively, proposed by محمد حلمي: 
=LET(
a,A2:A11,
c,C2:C11,
u,UNIQUE(c),
y,TAKE(u,1),
s,DROP(u,1),
e,LAMBDA(o,UNIQUE(TEXTSPLIT(CONCAT(
IF(c=o,a,"")&", "),,", "))),
i,LAMBDA(m,f,TEXTJOIN(", ",,
IF(ISNA(XMATCH(e(f),e(m))),e(f),""))),
HSTACK(u,VSTACK(i(s,y),i(y,s))))
Excel solution 4 for Subjects Taught Exclusively, proposed by 🇰🇷 Taeyong Shin: 
=LET(
    F,
    LAMBDA(
        x,
        TEXTSPLIT(
            ARRAYTOTEXT(
                x
            ),
            ,
            ", "
        )
    ),
    g,
    GROUPBY(
        C2:C11,
        A2:A11,
        LAMBDA(
            s,
            a,
            TEXTJOIN(
                ", ",
                ,
                UNIQUE(
                    TEXTSPLIT(
                        F(
                            a
                        ),
                        F(
                            s
                        )
                    )
                )
            )
        ),
        ,
        0
    ),
    IF(
        {1,
        0},
        g,
        SORTBY(
            g,
            {1;0}
        )
    )
)
Excel solution 5 for Subjects Taught Exclusively, proposed by Kris Jaganah: 
=LET(a,A1:A11,b,C1:C11,c,FILTER(a,b="Female"),d,FILTER(a,b="Male"),e,LEFT(TOCOL(TRIM(TEXTSPLIT(CONCAT(ARRAYTOTEXT(c)),",")),1),1),f,LEFT(TOCOL(TRIM(TEXTSPLIT(CONCAT(ARRAYTOTEXT(d)),",")),1),1),g,VSTACK(HSTACK("Male"&LEFT(e,0),e),HSTACK("Female"&LEFT(f,0),f)),h,UNIQUE(CHOOSECOLS(g,2)),i,BYROW(h,LAMBDA(x,SUM(IF((CHOOSECOLS(g,2)=x)*(CHOOSECOLS(g,1)="Female")=1,1,0)))),j,BYROW(h,LAMBDA(x,SUM(IF((CHOOSECOLS(g,2)=x)*(CHOOSECOLS(g,1)="Male")=1,1,0)))),k,MAP(i,j,LAMBDA(q,r,IF(AND(q=0,r>0),1,0))),l,MAP(i,j,LAMBDA(q,r,IF(AND(r=0,q>0),1,0))),m,ARRAYTOTEXT(FILTER(h,k=1)),n,ARRAYTOTEXT(FILTER(h,l=1)),o,VSTACK(HSTACK("Female",m),HSTACK("Male",n)),o)
Excel solution 6 for Subjects Taught Exclusively, proposed by Julian Poeltl: 
=LET(S,A2:A11,G,C2:C11,U,UNIQUE(TEXTSPLIT(TEXTJOIN(", ",,S),,", ")),L,LAMBDA(A,TEXTJOIN(", ",,FILTER(U,NOT(ISNUMBER(SEARCH(U,CONCAT(FILTER(S,G=A)))))))),HSTACK(VSTACK("Gender","Female","Male"),VSTACK("Missing Subjects",L("Male"),L("Female"))))
Excel solution 7 for Subjects Taught Exclusively, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _sb, A2:A11,
 _g, C2:C11,
 _ug, UNIQUE(_g),
 _j, MAP(_ug, LAMBDA(a, ARRAYTOTEXT(FILTER(_sb, _g = a)))),
 _c, MAP(
 _j,
 _ug,
 LAMBDA(a, b,
 LET(
 s, TEXTSPLIT(a, ", "),
 m, ISERR(FIND(s, FILTER(_j, _ug <> b))),
 c, BYCOL(m, LAMBDA(x, AND(x))),
 ARRAYTOTEXT(UNIQUE(FILTER(s, c, "None"), TRUE))
 )
 )
 ),
 _r, HSTACK(_ug, _c),
 _r
)
Excel solution 8 for Subjects Taught Exclusively, proposed by Timothée BLIOT: 
=LET(
    subjects,
     IFERROR(
         TEXTSPLIT(
             TEXTJOIN(
                 "/",
                 1,
                 A2:A11
             ),
             ", ",
             "/"
         ),
         ""
     ),
     Gender,
     C2:C11,
    
    Fsubjects,
     UNIQUE(
         TOCOL(
             FILTER(
                 subjects,
                 Gender="Female"
             )
         )
     ),
    
    Msubjects,
     UNIQUE(
         TOCOL(
             FILTER(
                 subjects,
                 NOT(
                     Gender="Female"
                 )
             )
         )
     ),
    
    FbutnotM,
     TEXTJOIN(
         ", ",
         1,
         MAP(
             Fsubjects,
              LAMBDA(
                  a,
                   IF(
                       ISNUMBER(
                           MATCH(
                               a,
                               Msubjects,
                               0
                           )
                       ),
                       "",
                       a
                   ) 
              )
         )
     ),
    
    MbutnotF,
     TEXTJOIN(
         ", ",
         1,
         MAP(
             Msubjects,
              LAMBDA(
                  a,
                   IF(
                       ISNUMBER(
                           MATCH(
                               a,
                               Fsubjects,
                               0
                           )
                       ),
                       "",
                       a
                   ) 
              )
         )
     ),
     VSTACK(
         HSTACK(
             {"Female"},
              FbutnotM
         ),
         HSTACK(
             {"Male"},
             MbutnotF
         )
     )
)
Excel solution 9 for Subjects Taught Exclusively, proposed by Owen Price: 
=LET(
d,$A$2:$C$11,s,INDEX(d,,1),g,INDEX(d,,3),y,", ",
getsubjects,LAMBDA(arr,UNIQUE(TEXTSPLIT(TEXTJOIN(y,,arr),,y))),
taughtby,LAMBDA(gender,getsubjects(FILTER(s,g<>gender))),
all,getsubjects(s),
genders,UNIQUE(g),
f,LAMBDA(gender,TEXTJOIN(y,,FILTER(all,ISERROR(XMATCH(all,taughtby(gender)))))),
VSTACK({"Gender","Missing Subjects"},HSTACK(genders,MAP(genders,f)))
)
Excel solution 10 for Subjects Taught Exclusively, proposed by Stefan Olsson: 
=BYROW(
LAMBDA(
    mf,
    
    {
     REGEXREPLACE(
         INDEX(
             mf,
             1,
             1
         ),
         "["&INDEX(
             mf,
             2,
             1
         )&"]",
         ","
     );
     REGEXREPLACE(
         INDEX(
             mf,
             2,
             1
         ),
         "["&INDEX(
             mf,
             1,
             1
         )&"]",
         ","
     )
    }
    
)(LAMBDA(
    ary,
    
     BYROW(
         UNIQUE(
             INDEX(
                 ary,
                 ,
                 3
             )
         ),
         
          LAMBDA(
              g,
              
               TEXTJOIN(
                   ",",
                   TRUE,
                   QUERY(
                       {ary},
                       "Select Col1 Where Col3 Like'"&g&"'",
                       0
                   )
               )
               
          )
          
     )
    
)(A2:C11)
),
    
 LAMBDA(
     rr,
     TEXTJOIN(
         ", ",
         TRUE,
         UNIQUE(
             SPLIT(
                 rr,
                 ",",
                 TRUE,
                 TRUE
             ),
             TRUE,
             FALSE
         )
     )
      
 )
)
Excel solution 11 for Subjects Taught Exclusively, proposed by El Badlis Mohd Marzudin: 
=LET(
    
    _sub,
    A2:A11,
    
    _gd,
    C2:C11,
    
    _ug,
    UNIQUE(
        _gd
    ),
    
    _join,
    CONCAT(
        MAP(
            UNIQUE(
        _gd
    ),
            LAMBDA(
                a,
                TEXTJOIN(
                    ", ",
                    1,
                    IF(
                        _gd=a,
                        _sub,
                        ""
                    )
                )
            )
        )&"|"
    ),
    
    _fs,
    UNIQUE(
        TEXTSPLIT(
            TEXTBEFORE(
                _join,
                "|"
            ),
            ,
            ", ",
            1
        )
    ),
    
    _ms,
    UNIQUE(
        SUBSTITUTE(
            TEXTSPLIT(
                TEXTAFTER(
                    _join,
                    "|"
                ),
                ,
                ", ",
                1
            ),
            "|",
            ""
        )
    ),
    
    _ufs,
    TEXTJOIN(
        ", ",
        1,
        FILTER(
            _fs,
            BYROW(
                _fs,
                LAMBDA(
                    x,
                    NOT(
                        ISNUMBER(
                            XMATCH(
                                x,
                                _ms
                            )
                        )
                    )
                )
            )
        )
    ),
    
    _ums,
    TEXTJOIN(
        ", ",
        1,
        FILTER(
            _ms,
            BYROW(
                _ms,
                LAMBDA(
                    y,
                    NOT(
                        ISNUMBER(
                            XMATCH(
                                y,
                                _fs
                            )
                        )
                    )
                )
            )
        )
    ),
    
    
    HSTACK(
        _ug,
        VSTACK(
            _ufs,
            _ums
        )
    )
)
Excel solution 12 for Subjects Taught Exclusively, proposed by RIJESH T.: 
=LET(
    s,
    A2:A11,
    g,
    C2:C11,
    f,
    TEXTSPLIT(
        ARRAYTOTEXT(
            FILTER(
                s,
                g=C6
            )
        ),
        ", "
    ),
    m,
    TEXTSPLIT(
        ARRAYTOTEXT(
            FILTER(
                s,
                g=C2
            )
        ),
        ", "
    ),
    HSTACK(
        UNIQUE(
            g
        ),
        VSTACK(
            ARRAYTOTEXT(
                UNIQUE(
                    FILTER(
                        m,
                        ISNA(
                            XMATCH(
                                m,
                                f
                            )
                        )
                    ),
                    1
                )
            ),
            ARRAYTOTEXT(
                FILTER(
                    f,
                    ISNA(
                        XMATCH(
                            f,
                            m
                        )
                    )
                )
            )
        )
    )
)

Solving the challenge of Subjects Taught Exclusively with SQL

SQL solution 1 for Subjects Taught Exclusively, proposed by Zoran Milokanović: 
WITH
DATA_PREPARATION
AS
(
 SELECT
 ROW_NUMBER() OVER () AS ORDINAL_NUMBER
 ,D.SUBJECTS
 ,LENGTH(REGEXP_REPLACE(D.SUBJECTS, '[^,]+', '')) /*NO_OF_DELIMITERS*/ + 1 AS NO_OF_SUBJECTS
 ,D.NAMES
 ,GENDER
 FROM DATA D
),
DATA_TRANSFORMATION
AS
(
 SELECT DISTINCT
 TRIM(SPLIT_PART(DP.SUBJECTS, ',', H.ORDINAL_NUMBER)) AS SUBJECT
 ,DP.GENDER
 FROM DATA_PREPARATION DP
 CROSS JOIN DATA_PREPARATION H
 WHERE
 1 = 1
 AND H.ORDINAL_NUMBER <= DP.NO_OF_SUBJECTS
 ORDER BY
 2, 1
)
SELECT
 DT1.GENDER
,REPLACE(LISTAGG(DT1.SUBJECT), ',', ', ') AS MISSING_SUBJECTS
FROM DATA_TRANSFORMATION DT1
(
 SELECT NULL FROM DATA_TRANSFORMATION DT2
 AND DT1.SUBJECT = DT2.SUBJECT
)
GROUP BY
 DT1.GENDER
ORDER BY
 1
;

Leave a Reply