Sort values in a virtual column

[BOTFORCE]

Hi all,

I have table with 36 columns for body-weight and -fatt measures [Körperfettmessungen]. Below you can see only 13 columns of them.

PZ-Kinn	PZ-Wange	PZ-Brust	PZ-Trizeps	PZ-Schulterblatt	PZ-Rippe	PZ-Hüfte	PZ-Bauch	PZ-Knie	PZ-Waden	PZ-vOberschenkel	PZ-hOberschenkel	PZ-Bizeps
0,97	0,86	3,18	0,80	1,24	0,89	0,28	3,87	0,36	0,18	0,37	1,92	0,34

I want to create in this table a virtual column (called: Priority) where only these 13 columnnames need to be displayed (sorted ) from the highest value to low value.

I have tried an SORT and Select expression as I thought a mutiple column select was possible like in SQL, unfortunately not. In Sheets you can select a column range and use the LARGE() function.

How can I get in my virtual column as text with the 13 columnnames (values are not even that relevant) to display from high to low. Below is an example in Google Sheets:

Thx!
Robert

[Steve]

Columns cannot be dynamically sorted.

[BOTFORCE]

Hi Steve,

is it possible the get a List in a virtual column from the values in the 13 columns?

Br.
Robert

[Steve · Accepted solution]

Yep. Set the virtual column’s App formula expression to:

LIST(
  [PZ-Kinn],
  [PZ-Wange],
  [PZ-Brust],
  [PZ-Trizeps],
  [PZ-Schulterblatt],
  [PZ-Rippe],
  [PZ-Hüfte],
  [PZ-Bauch],
  [PZ-Knie],
  [PZ-Waden],
  [PZ-vOberschenkel],
  [PZ-hOberschenkel],
  [PZ-Bizeps]
)

[BOTFORCE]

Steve,

one last question,

I applied also the SORT() for this List. Is it possible to do a lookup in this list and get the index / position in this list as return value?

Thx,
Robert

[Steve]

Robert:

Is it possible to do a lookup in this list and get the index / position in this list as return value?

Unfortunately, no.

[MultiTech]

@Steve, sure you can! (It’s just a complicated bit of magic)

Return an Index position from a list - Workaround available Questions

A complicated little bit of formula magic, but something that I’ve shared in the expression library at AppSheetTraining.com Here’s the formula: count( split( left( concatenate([Walkthrough_Link].[Walkthrough_Elements]), find( [ElementID], concatenate([Walkthrough_Link].[Walkthrough_Elements]) ) + 7 ), " , ") - list("") ) Here’s the video I made explaining it: A reformulation of the formula for your case would be something like this: count( split( left( concat…

haha same person

[Aleksi]

Not very nice and clean solution, but it should work when your decimals are less than 100.

SUBSTITUTE(
EXTRACTHASHTAGS(
TEXT(
SORT(
LIST(
RIGHT(“0”&[PZ-Kinn],5)&"#PZ-Kinn",
RIGHT(“0”&[PZ-Wange],5)&"#PZ-Wange",
RIGHT(“0”&[PZ-Brust],5)&"#PZ-Brust"),
TRUE)
)
)
,"#","")

[Marc_Dillon]

Aleksi:

Not very nice and clean solution

I disagree. Looks very nice to me. Well done!