how to use Join, Collect, match?

Brandon Morales

Hi, i have a sheet with a column with Job Codes and another column with Extrusion codes, im trying to use join collect on another sheet to get all of the different extrusion codes that are associated to each job code.

The sheet I want to collect the information to has a corresponding job code column so im trying to use "match" to join and collect the extrusion codes when the job codes match. so far ive been unsuccessful- any ideas?

my formula:

=JOIN(COLLECT({Extrusion Projections Range 1}, MATCH([Job Code / Batch]@row, {Extrusion Projections Range 2})))

Paul Newcome

You wouldn't use a MATCH function. It would just be a JOIN/COLLECT.

=JOIN(COLLECT({Range 1}, {Range 2}, @cell = [Job Code / Batch]@row), "delimiter of choice")

Brandon Morales

OK, i think im still a bit confused. Since im looking to collect info from multiple cells in the same column that match the job code, what would be "range 2"? maybe i shouldnt be using join? either way, im still getting unparseable.

Paul Newcome

{Range 1} and {Range 2} in my formula would be what you have specified as range 1 and range 2 in your formula. I just didn't type out the full range name is all.

Once you make those updates to my formula, if it is still not working, please provide a screenshot of the formula open in the sheet as if you are about to edit it.

Brandon Morales

still getting unparseable. here is my formula:

Paul Newcome

You need to include a delimiter and remove the last comma.

=JOIN(COLLECT({Range 1}, {Range 2}, @cell = [Job Code / Batch]@row), "delimiter of choice")

Brandon Morales

perfect! it worked. the comma was actually my delimiter but i didnt realize it needed to be in quotes…

Thanks so much for this help!!!!

Paul Newcome

Happy to help. 👍️