Joining Values Across Rows, Showing Only Unique Value
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?
Best Answer
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!