Joining Values Across Rows, Showing Only Unique Value

Art Schneiderheinze

Someone posted this response to a prior question I had about collecting values ("Modalities") across rows that have the same value in another column ("Subject+Course"):

=IF(COUNTIFS([Subject + Course]$1:[Subject + Course]@row, [Subject + Course]@row) = 1, JOIN(COLLECT(Modality:Modality, [Subject + Course]:[Subject + Course], [Subject + Course]@row, [Parent(1)/Child(0)]:[Parent(1)/Child(0)], OR(@cell = 0, @cell = 1)), " / "), "")

Here is an example:

SCI 101 | BL-10w/OL-10w | 2

• SCI 101 | BL-10w | 0
• SCI 101 | OL-10w | 0

It collects the modalities (BL-10w / OL-10w, in bold) from the rows that have the same course code (SCI 101). The Parent/Child is either 2, 1, or 0.

Works great!

Now, I'd like to use a similar approach, but only collect unique values.

SCI 101 | John Doe | 2

• SCI 101 | John Doe | 0
• SCI 101 | John Doe | 0

SCI 201 | John Doe / Lee Yang | 2

• SCI 201 | John Doe | 0
• SCI 201 | Lee Yang | 0

SCI 301 | Mary Drake | 1

For SCI 101, I want it just to show John Doe since it is a distinct value, where as, for SCI 201, there are two names, so it collects both names, separated with the slash. SCI 301 is only one row, so it just puts the only name there. (Note: Where I want it to put the name is not the same column as where the names exist.

Any ideas?

Mark Cronk

Try using DISTINCT:

=IF(COUNTIFS([Subject + Course]$1:[Subject + Course]@row, [Subject + Course]@row) = 1, JOIN(DISTINCT(COLLECT(Modality:Modality, [Subject + Course]:[Subject + Course], [Subject + Course]@row, [Parent(1)/Child(0)]:[Parent(1)/Child(0)], OR(@cell = 0, @cell = 1))), " / "), "")

Work?

Mark

Mark Cronk

Try using DISTINCT:

=IF(COUNTIFS([Subject + Course]$1:[Subject + Course]@row, [Subject + Course]@row) = 1, JOIN(DISTINCT(COLLECT(Modality:Modality, [Subject + Course]:[Subject + Course], [Subject + Course]@row, [Parent(1)/Child(0)]:[Parent(1)/Child(0)], OR(@cell = 0, @cell = 1))), " / "), "")

Work?

Mark