Join/Collect Criteria

I am using the following formula in Sheet 1 to collect a list of CRNs (course registration numbers) from Sheet 2 (the ranges in the formula reference columns in Sheet 2)::

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

CRN1 is a college prefix (e.g., UC for undergraduate college).

Essentially, this formula collects all of the CRNs in Sheet 2 based on three criteria. All of the criteria are applied to ranges in Sheet 2.

Works fine.

Is there a way to include a criteria that checks sheet 3 to see if that CRN is listed? And, if so, do not add it to the collection.

For example, if 12345 is in Sheet 2 and meets the current criteria in the formula. However, 12345 is listed in Sheet 3, so I would want the added criteria to check Sheet 3 and "skip it" if listed in Sheet 3. Otherwise collect and join to the list.

Find more posts tagged with

Sheets

Formulas

Comments

Debbie Sawyer

Have you tried adding this extra sheet 3 criteria first in a normal IF function.

i.e.

If 12345 is in Sheet 3 then do nothing, otherwise perform the Join(Collect) function already written?

So instead of trying to add the sheet 3 criteria inside the join(collect) function already written, you nest the already written function inside a simple IF function as the false statement.

Just an idea, I've not tested it!

Kind regards

Debbie

Paul Newcome

To expand on Debbie's suggestion...

You would use something along the lines of...

=IF(FIND("12345", JOIN({Sheet 3 CRN Column}, ",") = 0, your original formula)

Basically you are joining the CRN column on sheet 3 into a long text string. We use the comma as a delimiter to help avoid an overlap of CRN's throwing a false positive. For example...

654123

453217

Neither of them are 12345, but if we just JOIN them together without a delimiter we get

654123453217

WITH a delimiter

654123,453217

Now the FIND function will return a zero when searching for 12345 because that specific string is not found.

Since the FIND function returned a zero meaning it isn't in sheet 3, we run your original formula referencing sheet 2.

With an IF statement, if the criteria is not met and you didn't specify anything in the 3rd portion of the IF statement, it automatically defaults to blank.

=IF(this is true, then do this)

is the same as

=IF(this is true, then do this, else leave blank)

That little bit is really just a matter of preference.

Art Schneiderheinze

This sounds like a great solution. In this part:

=IF(FIND("12345", JOIN({Sheet 3 CRN Column}, ",") = 0, your original formula)

What do I put in as the "12345"? The complete list of CRNs is in Sheet 2, but I do not know what they? If the first CRN it collects is "12345", it should see if "12345" exists in Sheet 3. If it does not exist, it collects/joins. If it does exist, it skips it.

But what is the CRN it is looking for in Sheet 3? The CRN could be any 5-digit number, so I cannot put a specific number in quotes.

EXAMPLE:

On Sheet 2, the CRNs might be

12345

34343

38273

57333

23920

On Sheet 3, the CRNs to exclude might be

12345

23722

38273

89000

So, the formula in Sheet 1 would come up with 34343, 57333, 23920.

The formula, as is, would work if I knew the CRN to search for was 12345, but it could be any 5-digit number from the list in Sheet 2.

Art Schneiderheinze

I thought I'd be able to do something like this:

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

That is, using the formula you suggested, it collects the CRNs from Sheet 2, only if the CRN does not appear in Sheet 3.

{AutomatedCourseCopy-CRN} is the CRN pulled from Sheet 2 under consideration (the value in the cell for the CRN must not be blank, there must be a value in that run in the UseinBCC, the value of the College for that CRN must match CRN1 (a value I have in Sheet 1), and the CRN must not appear in the list of CRNs in Sheet 3.

{Round1-CRN} is the range of CRNs in Sheet 3

When I tried this, I get #INVALID DATA TYPE.

There are well over 500 CRNs in Sheet 2. Without the FIND criteria, it generates this in Sheet 1: "60450, 60237, 60338, 60226, 60231, 60356, 60434, 60451, 60376, 60378, 60390, 60066, 60379, 60105, 60067, 60313, 60319, 60314, 60064, 60087, 60385, 60047, 60386, 60387, 60032, 60027, 60028, 60598, 60455, 60107, 60068, 60472, 60448, 60188, 60209,"

This is the complete list of CRNs from Sheet 2 that match the original criteria. However, if, say, CRN 60450 and 60237 are list in Sheet 3, it should instead display this in Sheet 1: "60338, 60226, 60231, 60356, 60434, 60451, 60376, 60378, 60390, 60066, 60379, 60105, 60067, 60313, 60319, 60314, 60064, 60087, 60385, 60047, 60386, 60387, 60032, 60027, 60028, 60598, 60455, 60107, 60068, 60472, 60448, 60188, 60209,"

Now, those two CRNs are excluded because they appear in Sheet 3. The CRNs change each quarter, so I thought {AutomatedCourseCopy-CRN} represented the CRN under consideration from Sheet 2, which then I thought I could use the FIND function to see if it existed in Sheet 3.

Art Schneiderheinze

Makes sense, but I do not know the CRN ("12345" as just an example). It could be any 5-digit number, just depends what is included in Sheet 2. So, yes, if it can find the CRN first in Sheet 3, before collecting/joining it using my original formula, that would be great. However, how do know what to "look for" in Sheet 3 since I cannot just put a CRN in quotes for the FIND.

Paul Newcome

Ah. Ok. I see what you are saying now... I would suggest a checkbox helper column in sheet 2. In that column you can use...

=IF(FIND(CRN@row, JOIN({CRN Column Sheet 3}, ",")) = 0, 1)

This will check the box on sheet 2 if the CRN is NOT found on sheet 3.

You could then use your JOIN/COLLECT on sheet 1 with the added criteria of

@{Sheet 2 Helper Column}, @cell = 1

To tell the formula to pull based on your original criteria as long as the box in the helper column on sheet two is checked signifying that it is NOT on sheet 3.

Art Schneiderheinze

Paul, that was a great suggestion. Worked perfectly.

Debbie Sawyer

Well done! Great scenario and great solution.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…