Using Join collect from referenced sheet while ignoring criterion

Deonizia Egan · 10/21/24

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP",{Prime Student Onboarding Worksheet Affiliate}@row))), CHAR(10))

trying to get this formula to work while ignoring cells in the rate total column that have a hyphen (this is formula driven text). I have been all through the community discussions and tried <>"-", tried making that a "" instead, tried using not(has, and many other options.

Since the Affiliate row text SIP is what determines the hyphen in the total column I am now attempting to ignore anything that has SIP in that column and I am STILL coming up with incorrect argument. everything is giving me incorrect argument.

Hope someone way smarter than me can help. :)

Paul Newcome · 10/22/24

You are missing the range where SIP would be found.

You have

range, range, criteria, criteria

You need

range, range, criteria, range, criteria

The @cell tells the CONTAINS function to evaluate the previously established range (the one in bold above) on a cell by cell basis.

Paul Newcome · 10/22/24

The only issue is the range inside of the CONTAINS function. It should simply be "@cell" (without the quotes).

NOT(CONTAINS("SIP", @cell))

Deonizia Egan · 10/22/24

I tried it that way and it still didn't work. I tried just @cell (incorrect argument set), @row (bad syntax), or with the reference row mentioned as above. :(

@cell (incorrect argument set)

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, AND({Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP", @cell)))), CHAR(10))

@row (bad syntax)

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, AND({Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP", @row)))), CHAR(10))

with reference column (incorrect argument set)

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, AND({Prime Student Onboarding Worksheet School}, School@row, NOT(HAS({Prime Student Onboarding Worksheet Affiliate} = "SIP")))), CHAR(10))

this is my original working formula:

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row), CHAR(10))

it produces this cell data

does it matter if the data "SIP" is coming from a vlookup?

Paul Newcome · 10/22/24

The @cell would work, but it looks like you added an AND function in there for some reason. If you remove that along with the associated set of parenthesis and have it follow the appropriate syntax for the COLLECT function (range, range, criteria, range, criteria), it should work.

Deonizia Egan · 10/22/24

this?

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP", @cell))), CHAR(10))

I am now getting an invalid operation error.

Also, can you help me understand to what Cell the "@cell" is pointing? how does it know what column to look in if the column isn't one of the options in the formula?

Paul Newcome · 10/22/24

You are missing the range where SIP would be found.

You have

range, range, criteria, criteria

You need

range, range, criteria, range, criteria

The @cell tells the CONTAINS function to evaluate the previously established range (the one in bold above) on a cell by cell basis.

Deonizia Egan · 10/22/24

WOW! ok. that was way easier than I was trying to do. I tried about a million things and was probably just a little bit off each time. 😝 Thank you! it is fixed.

Deonizia Egan · 10/22/24

UGH! Now I tried to use the same formula to gather the same criteria data off another column and i get incorrect argument again! Bolded is the ONLY thing I changed (new reference column from same referenced sheet as before)

=JOIN(COLLECT({Prime Student Onboarding Worksheet Student}, {Prime Student Onboarding Worksheet School}, School@row, {Prime Student Onboarding Worksheet Rate Total}, NOT(CONTAINS("-", @cell))), CHAR(10))

I did change up the location for the "not(contains" because apparently not ALL of the "-" corresponded to SIP after all. but it works in the original formula

=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, {Prime Student Onboarding Worksheet Rate Total}, NOT(CONTAINS("-", @cell))), CHAR(10))

so now I just need to pull the student names.

Paul Newcome · 10/23/24

Make sure all ranges are the same size and shape. If you are referencing individual columns, make sure you are clicking on the column header when creating the cross sheet reference. Also give it a little time to load. Sometimes the sheet doesn't fully load in before it becomes visible in the popup window. Once it does fully load, the range will revert to the "home cell" (top left cell in the sheet) and you have to re-select your range.

Deonizia Egan · 10/24/24

ok. you were right, but that took a LONG time to load. 🙄

I just refreshed it this morning and it was there, so sometime during the night it finally loaded. Thanks!😃

Paul Newcome · 10/24/24

I've never had it take more than a minute or two to load really large / complex sheets. I would suggest trying it again a few times to see if it is still that slow. I fit is, you may want to reach out to support.

Formulas and Functions

Formulas and Functions

Using Join collect from referenced sheet while ignoring criterion

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary