Log In
Register
Home
February 9, 2022
Offline
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?
See Answer
Trusted Members
October 18, 2018
Offline
Will there ever be more than three columns with numbers between 1 and 4?
August 21, 2019
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
February 9, 2022
Offline
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;"")
Answers Post
1 Guest(s)
