List column headers where value is true

[JimSpringer]

I have a list generated from another program of all of the locations one person worked at. It looks like this. The order of the locations is listed randomly:

Gary	Linda	Marissa	Brad	Xavier
Lincoln	Wausau	Annapolis	Laredo	Bethesda
Laredo		Bethesda	Waco	Annapolis
St Louis				Wausau

<tbody>
</tbody>

I was able to get it into a format like this:

	Brad	Gary	Linda	Marissa	Xavier
Annapolis	NO	NO	NO	YES	YES
Bethesda	NO	NO	NO	YES	YES
Laredo	YES	YES	NO	NO	NO
Lincoln	NO	YES	NO	NO	NO
St Louis	NO	YES	NO	NO	NO
Waco	YES	NO	NO	NO	NO
Wausau	NO	NO	YES	NO	YES

<tbody>
</tbody>

What I need to generate is a list like this one:

Annapolis	Bethesda	Laredo	Lincoln	St Louis	Waco	Wausau
Marissa	Marissa	Brad	Gary	Gary	Brad	Linda
Xavier	Xavier	Gary				Xavier

<tbody>
</tbody>

Ideally, I would generate it from the first table, without having to make the second one. I am stuck, however, on how to make this happen. It seems like it should be simple, but I can't get it to work. Asking for help.

 

[sandy666]

you can try PowerQuery aka Get&Transform

Gary

Linda

Marissa

Brad

Xavier

Annapolis

Bethesda

Laredo

Lincoln

St Louis

Waco

Wausau

Lincoln

Wausau

Annapolis

Laredo

Bethesda

Marissa

Xavier

Brad

Gary

Gary

Brad

Linda

Laredo

Bethesda

Waco

Annapolis

Xavier

Marissa

Gary

Xavier

St Louis

Wausau

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Value"}, {{"Count", each _, type table}}),
    TblList = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Attribute")),
    Extract = Table.TransformColumns(TblList, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RC = Table.RemoveColumns(Extract,{"Count"}),
    Split = Table.SplitColumn(RC, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    Sort = Table.Sort(Split,{{"Value", Order.Ascending}}),
    Transpose = Table.Transpose(Sort),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    Promote[/SIZE]

 

[MickG]

Anther option:-
Results start "G1".

[COLOR="Navy"]Sub[/COLOR] MG17Apr20
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, P [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 Ray = Cells(1).CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
   [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
   [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR] .Add Ray(n, Ac), New Collection
                .Item(CStr(Ray(n, Ac))).Add CStr(Ray(1, Ac)), CStr(Ray(1, Ac))
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac

Ac = 6
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Ac = Ac + 1
    Cells(1, Ac) = K: c = 1
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] .Item(K)
            c = c + 1
            Cells(c, Ac) = P
        [COLOR="Navy"]Next[/COLOR] P
[COLOR="Navy"]Next[/COLOR] K

[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[JimSpringer]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Value"}, {{"Count", each _, type table}}),
    TblList = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Attribute")),
    Extract = Table.TransformColumns(TblList, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RC = Table.RemoveColumns(Extract,{"Count"}),
    Split = Table.SplitColumn(RC, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    Sort = Table.Sort(Split,{{"Value", Order.Ascending}}),
    Transpose = Table.Transpose(Sort),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    Promote[/SIZE]

[/QUOTE]
Jaw on the floor. I did not know about any of this! Thank you!

One thing I can't quite figure out - the query winds up returning only two rows. The real table I'm using is quite a bit larger, but if there are, say, fourteen sales reps in a city, it stops after the first two of them. So for an example, in a column where six people are assigned to Chicago, what I see is:

Chicago

Anne

Brenda

<tbody>
</tbody>

Is there something I could tweak to get the missing reps listed?

 

[JimSpringer]

This is blowing my mind. I did not know how to do any of this, and it is so much fun. I wasted a day trying to figure out how do do this with MATCH/INDEX. My world is expanded.

Thank you both!

 

[sandy666]

so try this

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Value"}, {{"Count", each _, type table}}),
    TblList = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Attribute")),
    Extract = Table.TransformColumns(TblList, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RC = Table.RemoveColumns(Extract,{"Count"}),
    DelimiterCount = Table.AddColumn(RC, "CountDelimiters", each List.Count(Text.Split([Custom],","))-1),
    MaxCount = List.Max(DelimiterCount[CountDelimiters])+1,
    Split = Table.SplitColumn(RC, "Custom", Splitter.SplitTextByDelimiter(","),MaxCount),
    Sort = Table.Sort(Split,{{"Value", Order.Ascending}}),
    Transpose = Table.Transpose(Sort),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    Promote[/SIZE]

or post more representative example

 

[JimSpringer]

That is a much classier solution than the one I came up with

 

[sandy666]

I don't see your solution so I can't say too much