I would like to make a column that returns a ...

(Diogenes ASBL - VZW) #1

I would like to make a column that returns a list of values of the column [Teammembers] of orders they had from the column [order]

So the column returns for example: for Order 1: teammember 1, teammember 3 for Order 2: teammember 2, teammember 3,…

What would be a good formula? Can this also in an fysical column? Not a virtual column?

Bram

(Steven Coile) #2

physical

(Steven Coile) #3

Team members with order 1:

=SELECT(MyTable[teammember], ([order] = “Order 1”))

Team members with order 2:

=SELECT(MyTable[teammember], ([order] = “Order 2”))

You’d probably want this yo be a virtual column.

(Diogenes ASBL - VZW) #4

Thanks Steve, An additional question: you have an idea how to get unique values for [teammember]?

So when Stephano has 2 times Order 2 in the tabel I get his name only 1 time in the list?

in google sheets it would be Sort(unique(SELECT(MyTable[teammember], ([order] = “Order 2”))))

thx a lot, Bram

(Steven Coile) #5

Try subtracting an empty list:

SELECT(MyTable[teammember], ([order] = “Order 2”)) - LIST()

In fact, any list subtraction will remove duplicates from a list. I’m not sure this is “supported” behavior, so it might change in the future, but for the time-being, it’s the only option I’m aware of.

(Diogenes ASBL - VZW) #6

Thx Steve

… that’s working!