Join/Collect Criteria

Art Schneiderheinze
Art Schneiderheinze ✭✭
edited 12/09/19 in Formulas and Functions

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.

 

 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Debbie Sawyer
    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

    · Share on FacebookShare on Twitter
  • Paul Newcome
    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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Art Schneiderheinze
    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.

    · Share on FacebookShare on Twitter
  • Art Schneiderheinze
    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.

    · Share on FacebookShare on Twitter
  • Art Schneiderheinze
    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.

     

    · Share on FacebookShare on Twitter
  • Paul Newcome
    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.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Art Schneiderheinze
    Art Schneiderheinze ✭✭

    Paul, that was a great suggestion. Worked perfectly. 

    · Share on FacebookShare on Twitter
  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Well done! Great scenario and great solution.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!