how to get commas between join/collect of multiple sheets

PaigeH

I have a formula.. a very long one 😂 that is pulling numbers from multiple sheets however it is only putting comma/spaces between numbers from the same sheet. Numbers from different sheets are just mashed together. Is there a way to fix this?

=JOIN(COLLECT({Deviation Tracking Log- QMS Number}, {Deviation Tracking Log- Associated Lot Numbers}, CONTAINS([Product Lot Number]@row, @cell), {2023 Deviation Tracking Log- QMS Status}, "Open"), ", ") + JOIN(COLLECT({2022 Deviation Tracking Log- QMS Number}, {2022 Deviation Tracking Log Range 3}, CONTAINS([Product Lot Number]@row, @cell), {2022 Deviation Tracking Log- QMS Status}, "Open"), ", ") + JOIN(COLLECT({2023 CAPA Tracking-QMS Number}, {2023 CAPA Tracking- associated lot number}, CONTAINS([Product Lot Number]@row, @cell), {2023 CAPA Tracking QMS-Status}, "Open"), ", ") + JOIN(COLLECT({2023 Change Control Tracking-QMS #}, {2023 Change Control Tracking- LOT #}, CONTAINS([Product Lot Number]@row, @cell), {2023 Change Control Tracking- QMS STATS}, "Open"), ", ") + JOIN(COLLECT({2023 Out of Specification Tracking- QMS#}, {2023 Out of Specification Tracking- Lot Num}, CONTAINS([Product Lot Number]@row, @cell), {Out of Specification Tracking- QMS Status}, "Open"), ", ") + JOIN(COLLECT({2023 Invalid Tracking- QMS Num}, {2023 Invalid Tracking- Lot Number}, CONTAINS([Product Lot Number]@row, @cell), {Invalid Tracking- QMS status}, "Open"), " , ") + JOIN(COLLECT({EAR Tracking Log - Historical QMS Number}, {EAR Tracking Log - Historical Associate Lot Number}, CONTAINS([Product Lot Number]@row, @cell), {EAR Tracking Log - Historical QMS Status}, "Open"), ", ") + JOIN(COLLECT({EAR Tracking Log QMS Number}, {EAR Tracking Log Associate Lot Number}, CONTAINS([Product Lot Number]@row, @cell), {EAR Tracking Log QMS Status}, "Open"), ", ")

Kelly Moore

Hey @PaigeH

try replacing the pluses you have in front of your JOIN with this:

+", "+

Does that work for you?

Kelly

PaigeH

That sort of works but it puts commas even in the blank fields now

PaigeH

if i update it that way throughout the whole formula, this is what it looks like

Toufong Vang

The issue is caused by your use of "," to separate your JOIN(COLLECT()) expressions. E.g., JOIN(1) + "," + JOIN(2) + "," JOIN(3) + "," +…+ JOIN(8).

To avoid the extraneous commas, one approach is to use helper columns and then JOIN(COLLECT()) them.

=JOIN(COLLECT(JOIN1:JOIN8, JOIN1:JOIN8, <>""), ",")

The alternative is to check if the JOIN() expression before and after the comma in your original formula is NOT(ISBLANK(JOIN_EXPRESSION)) and, if true, then include the "," between the joins. This approach will further spaghettify your expression.