Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Wondering if there is any way to retrieve field name associated with range max value. I don't want to use crosstable and then firstsorted value.
A | B | C | Field |
10 | 15 | 23 | C |
10 | 16 | 2 | B |
Thanks
May be this
Pick(Match(RangeMax(A, B, C), A, B, C), 'A', 'B', 'C') as Field,
May be this
Pick(Match(RangeMax(A, B, C), A, B, C), 'A', 'B', 'C') as Field,
Here is a sample from your data
This is a good solution. Sunny, there are more than 150 field that's why i didn't want to do crosstable. Here also i will have to write all fields name. Any alternate direct function in your knowledge?
i mean similar to firstsorted value in array
Not sure bro. I have never seen a function pull field name using a value within the field. I mean there are FieldValue() functions, but not sure how they can be used here. I will keep testing and let you know
I tried all fieldsuffix function, not worked...but thanks.
I will keep digging. May be swuehl can offer his expert advice also
You don't necessarily have to list all fieldnames in a cross table. You can use "*". What does your data look like?
You can generate the pick(match()) dynamically in a chart as:
=Pick(Match(RangeMax(
$(=concat([$Field],','))
),
$(=concat([$Field],','))
),
$(=
chr(39)
& concat([$Field], chr(39) & ',' & chr(39))
& chr(39)
)
)
You can limit the field list to a particular table by adding a bit of <$Table={xyz}> set analysis into the concat(). Or exclude certain fields.
You can do something similar in the script. but yu would have to build up the field list in a variable loop using the fieldname() function. A bit more work.
-Rob