JOIN, COLLECT with MATCH

Art Schneiderheinze · 05/19/19

In Sheet 2, I want to JOIN and COLLECT a group of values from Sheet 1, separated by a comma. I have this figured out:

=JOIN(COLLECT({AutomatedCourseCopy-CRN}, {AutomatedCourseCopy-CRN}, <>"", {AutomatedCourseCopy-UseinBCC}, <>"", {AutomatedCourseCopy-College}, CRN1), ", ") + ","

I have a set of values in Sheet 3 that should be excluded from the JOIN list. However, I am not sure how to include this criteria in the above JOIN and COLLECT formula.

FOR EXAMPLE

Sheet 1 (report of all fruits, even if I already took care of the fruits in round 1)

-----------------

Prunes

Oranges

Apples

Plums

Pears

Sheet 2 (automatically displays list of fruits from Sheet 1)

-----------------

Join/Collect those that start with a "P":

RESULT: Prunes, Plums, Pears

Sheet 3

(I manually copied/pasted "Plums" because I already took care of plums in round 1.)

-----------------

Plums

Updated Sheet 2 (now with additional criteria to exclude values listed in Sheet 3:

-----------------

RESULT: Prunes, Pears

I am automating a copy process into live course sites TWICE before the term begins, based on an enrollment report in another system. I run the report, and copy/paste the information from the report into Sheet 1. Sheet 2 now updates by joining-collecting course information from Sheet 1.

On the second time running the copy process, I pull the enrollment report, but it still has the courses that were included in the first run, so to avoid copying the courses again, I copy/paste the courses I copied in round 1 into Sheet 3. So, on the second time running the process, Sheet 2 only displays the courses from the enrollment report (Sheet 1) that were not run in round 1 (Sheet 3).

Can the JOIN-COLLECT formula include a critreria of some sort that excludes an item from Sheet 1 if it is found in Sheet 3?

=JOIN(COLLECT({AutomatedCourseCopy-CRN}, {AutomatedCourseCopy-CRN}, <>"", {AutomatedCourseCopy-UseinBCC}, <>"", {AutomatedCourseCopy-College}, CRN1, MATCH ???), ", ") + ","

{AutomatedCourseCopy-CRN} is range in Sheet 1, it includes the value if cell isn't blank
{AutomatedCourseCopy-UseinBCC} is range in Sheet 1, it includes the value if cell isn't blank
{AutomatedCourseCopy-College} is range in Sheet 1, include if it matches value in Sheet 2
MATCH???, criteria that does NOT collect the item if it appears in range in Sheet 3

Paul Newcome · 05/20/19

First things first... The MATCH function actually returns a number based on where the specified data is found within a range that is broken down into a grid.

What we really want is something along the lines of this...

...........{Range You Want To Compare To Sheet 3}, FIND(@cell, JOIN({Range On Sheet 3}) = 0...............

.

I honestly wasn't sure if this would work at first, so I created three sheets. Sheet one had 11 rows of data. Sheet two had 11 rows of the same exact data. Sheet three is where I was working. I used a COUNTIFS function to test. First I counted my range on sheet one just to be sure.

=COUNTIFS({Sheet 1}, ISTEXT(@cell))

The result was 11 as expected.

My next test was

=COUNTIFS({Sheet 1}, ISTEXT(@cell), @{Sheet 1}, FIND(@cell, JOIN({Sheet 2})) > 0)

Gave a result of 11 again.

I then changed it from greater than 0 to equal to 0, and the result came back as 0 which tells me that each cell in sheet one had a match on sheet two. My next two tests changed some text on each sheet (one at a time) and the counts updated accordingly.

I am honestly kind of surprised that it worked.

JOIN, COLLECT with MATCH

Comments

Help Article Resources

Categories