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)::
=加入(收集({AutomatedCourseCopy-CRN},{自动化dCourseCopy-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.
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.
-
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.
-
I thought I'd be able to do something like this:
=加入(收集({AutomatedCourseCopy-CRN},{自动化dCourseCopy-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.
-
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.
-
Paul, that was a great suggestion. Worked perfectly.
-
Debbie Sawyer ✭✭✭✭✭✭
Well done! Great scenario and great solution.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"