How can i find DISTINCT names from multiple JOIN(Distinct(Collect)

I am collecting instructor names for courses from multiple sheets which works fine, but it may list the same person multiple times as it finds them in each sheet.

=JOIN(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccsp23}, {cnsp23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccfa22}, {F22CN}, Course@row)))

Is it possible to wrap the searches below around DISTINCT to show one unique name? i.e. =DISTINCT(JOIN(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccsp23}, {cnsp23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccfa22}, {F22CN}, Course@row))))

Find more posts tagged with

Accepted answers

All comments

J Tech

Hi @gwson,

You could try:

=JOIN(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row) + COLLECT({ccsp23}, {cnsp23}, Course@row) + COLLECT({ccfa22}, {F22CN}, Course@row)))

gwson

I get an invalid operation error.

Darren Mullen

@gwson unfortunately you can't concentrate ranges, so you'll have to create a larger range first.

I did this for a client recently. I'll work a demo up and do a video. Stay tuned!

Paul Newcome

My suggestion is to use some helper columns to parse each distinct list into their own column then run a JOIN/DISTINCT on these helper columns.

First insert a text/number column (called "Number" in this example) with numbers manually entered starting with 1 and going down as far as you need.

so on and so forth.

Then in the next column you would use an INDEX/DISTINCT/COLLECT and reference the Number column.

=IFERROR(INDEX(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row)), Number@row), "")

You would then duplicate this for the next column over pulling in your second list and then again in another column to get your third list.

Then your JOIN would look more like:

=JOIN(DISTINCT(COLLECT([Helper 1]:[Helper 3], [Helper 1]:[Helper 3], @cell <> "")), "delimiter of choice")

Darren Mullen

@gwson @Paul Newcome Yes, what Paul said is the way. Also, you may need to filter out blank cells when you collect over that range of helper cells, but that is easy using not(isblank(@cell)) as a criterion in the collect function.

I did make a video inspired by your question, so you can see it in action. I don't show using the JOIN() function, but otherwise, you'll do the same thing and it also shows about using not(isblank(@cell)) as a criterion in the collect function.

https://youtu.be/HfE0Yyj3pzc?si=GkD0OIKbsR0Q7lft

Paul Newcome

@Darren Mullen Mine does exclude blanks. Thats where the <> "" piece comes into play.

I prefer that over NOT(ISBLANK(@cell)) because not only is it less work my fingers have to do (haha), but it is also less parenthesis to accidentally misplace.

Darren Mullen

@Paul Newcome Ahh, I missed that in your formula :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.