Hi,
i am struggling with the following;
The columns having a value between 0 - 4
If value is 1 or higher, I want to have the column header,
In the screenshot, I added 4 column with value 1,2,3,4.
there is need the result of the value.
So for example in row 9
In my value 1 I need 'Transito' (value in c9)
And in my value 2 I need 'Type_agaricus' (Value in H9)
Anyone have a smart idea to extract this?
Will there ever be more than three columns with numbers between 1 and 4?
Hi,
easier to do it in Power Query
Worksheet one is your source which consist only raw columns, bring into Power Query, the following are the M Code
let
Source = Table1,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Index", "Groep", "Latijns"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value]>= 1 then [Attribute] else ""),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",Headers)
in
#"Renamed Columns"
You can amend the values in some of the columns to more than 1, and goto second tab the output table, right click and refresh to see the updated result
Rgds
For a formula version, in C2:
=IFERROR(INDEX($G$1:$Y$1,AGGREGATE(15,6,1/($G2:$Y2>0)*(COLUMN($G2:$Y2)-COLUMN($G2)+1),COLUMNS($A$1:A$1))),"")
and copy across and down.
Good solutions Velouria and Chris,
Chris, i am to nooby for understand what you did there =) but for now it worked.
Velouria, thanks tho!
i also figured out later, that the =filter worked here
=FILTER(G$1:Y$1;G2:Y2>0;"")
