Return all column headers where the cells are not blank

[rubbis_with_excel]

Hi,
I'm really struggling with a formula and I was hoping someone might be able to help.

I have a table such as this:

Name__System1__System2__System3
........................................................
Adam__100%....__0%.......__0%
Bill....__50%.....__50%......__0%
Claire__25%.....__25%......__50%

And in a separate sheet I want a formula to show each header when the relevant person's cell is not blank. Such as:

Name__Systems
........................
Adam__System1
Bill....__System1,System2
Claire__System1,System2,System3

The problem is that I can't use VBA and I can't add any columns or rows, so I need to house everything in the cell only.

Does anyone have any tidy solutions in mind?

Thanks

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Name	System1	System2	System3
2	Adam	100%	0%	0%
3	Bill	50%	50%	0%
4	Claire	25%	25%	50%
Sheet1

Book1
	A	B	C	D
1	Name	System
2	Adam	1
3	Bill	0.5	0.5
4	Claire	0.25	0.25	0.5
Sheet2

In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$B$2:$D$4,MATCH($A2,Sheet1!$A$2:$A$4,0),SMALL(IF($A$2:$A$4=$A2,IF(ISNUMBER(1/Sheet1!$B$2:$D$4),IF(Sheet1!$B$2:$D$4>0,COLUMN(Sheet1!$B$2:$D$4)-COLUMN(Sheet1!$B$2)+1))),COLUMNS($B2:B2))),"")

 

[rubbis_with_excel]

Thank you!!!

 

[Aladin Akyurek]

Thank you!!!

You are welcome.

 

[sandy666]

Such as:

Name__Systems
........................
Adam__System1
Bill....__System1,System2
Claire__System1,System2,System3

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]System1[/td][td=bgcolor:#5B9BD5]System2[/td][td=bgcolor:#5B9BD5]System3[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]System[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Adam[/td][td=bgcolor:#DDEBF7]

100%​

[/td][td=bgcolor:#DDEBF7]

0%​

[/td][td=bgcolor:#DDEBF7]

0%​

[/td][td][/td][td=bgcolor:#E2EFDA]Adam[/td][td=bgcolor:#E2EFDA]System1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bill[/td][td]

50%​

[/td][td]

50%​

[/td][td]

0%​

[/td][td][/td][td]Bill[/td][td]System1,System2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Claire[/td][td=bgcolor:#DDEBF7]

25%​

[/td][td=bgcolor:#DDEBF7]

25%​

[/td][td=bgcolor:#DDEBF7]

50%​

[/td][td][/td][td=bgcolor:#E2EFDA]Claire[/td][td=bgcolor:#E2EFDA]System1,System2,System3[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "System", each Table.Column([Count],"Attribute")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"System", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"Count"})
in
    #"Removed Columns1"[/SIZE]

result table can be loaded anywhere