Issues With Extra Commas When Joining Values of Multiple Columns Into One

Benjamin O'Leary

Hello,

I am using the following formula to take values within multiple columns into one column:

=IF(ISBLANK([Automation (Corelab)]@row), "", [Automation (Corelab)]@row + ", ") + 

IF(ISBLANK([Cardiometabolic (Corelab)]@row), "", [Cardiometabolic (Corelab)]@row + ", ") + 

IF(ISBLANK([Coagulation, Hematology, Urinanalysis (Corelab)]@row), "", [Coagulation, Hematology, Urinanalysis (Corelab)]@row + ", ") + 

IF(ISBLANK([CoreLab Reagents (Corelab)]@row), "", [CoreLab Reagents (Corelab)]@row + ", ") + 

IF(ISBLANK([Custom Biotech (Corelab)]@row), "", [Custom Biotech (Corelab)]@row + ", ") + 

IF(ISBLANK([Infectious Disease/RBSS (Corelab)]@row), "", [Infectious Disease/RBSS (Corelab)]@row + ", ") + 

IF(ISBLANK([Oncology (Corelab)]@row), "", [Oncology (Corelab)]@row + ", ") + 

IF(ISBLANK([SWA Systems (Corelab)]@row), "", [SWA Systems (Corelab)]@row + ", ") + 

IF(ISBLANK([Blood Screening (Molecular Lab)]@row), "", [Blood Screening (Molecular Lab)]@row + ", ") + 

IF(ISBLANK([Infectious Diseases (Molecular Lab)]@row), "", [Infectious Diseases (Molecular Lab)]@row + ", ") + 

IF(ISBLANK([Oncology & Genetics (Molecular Lab)]@row), "", [Oncology & Genetics (Molecular Lab)]@row + ", ") + 

IF(ISBLANK([PCR Systems Workflow & IT (Molecular Lab)]@row), "", [PCR Systems Workflow & IT (Molecular Lab)]@row + ", ") + 

IF(ISBLANK([Seq Systems (incl. Informatics) (Molecular Lab)]@row), "", [Seq Systems (incl. Informatics) (Molecular Lab)]@row + ", ") + 

IF(ISBLANK([Digital Pathology (Pathology Lab)]@row), "", [Digital Pathology (Pathology Lab)]@row + ", ") + 

IF(ISBLANK([Oncology Assays (Pathology Lab)]@row), "", [Oncology Assays (Pathology Lab)]@row + ", ") + 

IF(ISBLANK([Personalized Health Care Solutions (Pathology Lab)]@row), "", [Personalized Health Care Solutions (Pathology Lab)]@row + ", ") + 

IF(ISBLANK([Systems Workflow & IT (Pathology Lab)]@row), "", [Systems Workflow & IT (Pathology Lab)]@row + ", ") + 

IF(ISBLANK([Blood Gas/ Hospital Blood Glucose (Point of Care)]@row), "", [Blood Gas/ Hospital Blood Glucose (Point of Care)]@row + ", ") + 

IF(ISBLANK([Clinical Chem/ Immunology (Point of Care)]@row), "", [Clinical Chem/ Immunology (Point of Care)]@row + ", ") + [LIAT (Point of Care)]@row

While it works, I am getting random commas throughout the column, and I don't know how to properly remove them:

Any help would be greatly appreciated. Thank you.

Carson Penticuff

Is it possible these random commas are already in the cells you are pulling in?

Benjamin O'Leary

@Carson Penticuff the columns I am pulling in are all dropdown columns that allow multiple values. Could that be the issue? I don't see any commas in those values.

Andrée Starå

Hi @Benjamin O'Leary

I hope you're well and safe!

Have you explored using the JOIN function instead?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Benjamin O'Leary

@Andrée Starå I have a JOIN statement as well:

=JOIN([Automation (Corelab)]@row:

[Cardiometabolic (Corelab)]@row:

[Coagulation, Hematology, Urinanalysis (Corelab)]@row:

[CoreLab Reagents (Corelab)]@row:

[Custom Biotech (Corelab)]@row:

[Infectious Disease/RBSS (Corelab)]@row:

[Oncology (Corelab)]@row:

[SWA Systems (Corelab)]@row:

[Blood Screening (Molecular Lab)]@row:

[Infectious Diseases (Molecular Lab)]@row:

[Oncology & Genetics (Molecular Lab)]@row:

[PCR Systems Workflow & IT (Molecular Lab)]@row:

[Seq Systems (incl. Informatics) (Molecular Lab)]@row:

[Digital Pathology (Pathology Lab)]@row:

[Oncology Assays (Pathology Lab)]@row:

[Personalized Health Care Solutions (Pathology Lab)]@row:

[Systems Workflow & IT (Pathology Lab)]@row:

[Blood Gas/ Hospital Blood Glucose (Point of Care)]@row:

[Clinical Chem/ Immunology (Point of Care)]@row:

[LIAT (Point of Care)]@row, ", ")

But I get an #UNPARSEABLE error.

Hollie Green

I would try the below formula

=IF(ISBLANK([Automation (Corelab)]@row), "", JOIN([Automation (Corelab)]@row , ", ") +

IF(ISBLANK([Cardiometabolic (Corelab)]@row), "", JOIN([Cardiometabolic (Corelab)]@row , ", ") +

IF(ISBLANK([Coagulation, Hematology, Urinanalysis (Corelab)]@row), "", JOIN([Coagulation, Hematology, Urinanalysis (Corelab)]@row , ", ") +

IF(ISBLANK([CoreLab Reagents (Corelab)]@row), "", JOIN([CoreLab Reagents (Corelab)]@row , ", ") +

IF(ISBLANK([Custom Biotech (Corelab)]@row), "", JOIN([Custom Biotech (Corelab)]@row , ", ") +

IF(ISBLANK([Infectious Disease/RBSS (Corelab)]@row), "", JOIN([Infectious Disease/RBSS (Corelab)]@row , ", ") +

IF(ISBLANK([Oncology (Corelab)]@row), "", JOIN([Oncology (Corelab)]@row , ", ") +

IF(ISBLANK([SWA Systems (Corelab)]@row), "", JOIN([SWA Systems (Corelab)]@row , ", ") +

IF(ISBLANK([Blood Screening (Molecular Lab)]@row), "", JOIN([Blood Screening (Molecular Lab)]@row , ", ") +

IF(ISBLANK([Infectious Diseases (Molecular Lab)]@row), "", JOIN([Infectious Diseases (Molecular Lab)]@row, ", ") +

IF(ISBLANK([Oncology & Genetics (Molecular Lab)]@row), "",JOIN( [Oncology & Genetics (Molecular Lab)]@row,", ") +

IF(ISBLANK([PCR Systems Workflow & IT (Molecular Lab)]@row), "", JOIN([PCR Systems Workflow & IT (Molecular Lab)]@row ,", ") +

IF(ISBLANK([Seq Systems (incl. Informatics) (Molecular Lab)]@row), "",JOIN( [Seq Systems (incl. Informatics) (Molecular Lab)]@row , ", ") +

IF(ISBLANK([Digital Pathology (Pathology Lab)]@row), "", JOIN([Digital Pathology (Pathology Lab)]@row , ", ") +

IF(ISBLANK([Oncology Assays (Pathology Lab)]@row), "", JOIN([Oncology Assays (Pathology Lab)]@row , ", ") +

IF(ISBLANK([Personalized Health Care Solutions (Pathology Lab)]@row), "", JOIN([Personalized Health Care Solutions (Pathology Lab)]@row , ", ") +

IF(ISBLANK([Systems Workflow & IT (Pathology Lab)]@row), "", JOIN([Systems Workflow & IT (Pathology Lab)]@row , ", ") +

IF(ISBLANK([Blood Gas/ Hospital Blood Glucose (Point of Care)]@row), "", JOIN([Blood Gas/ Hospital Blood Glucose (Point of Care)]@row , ", ") +

IF(ISBLANK([Clinical Chem/ Immunology (Point of Care)]@row), "", JOIN([Clinical Chem/ Immunology (Point of Care)]@row , ", ") +

IF(ISBLANK[LIAT (Point of Care)]@row,"",JOIN([LIAT (Point of Care)]@row,", ")

Benjamin O'Leary

@Hollie Green the formula you created works, though I am getting an "INVALID OPERATION error on the second column:

Could it be because there are duplicate options? Users are able to select an option that are present in more than one column:

Benjamin O'Leary

@Hollie Green also, the different options are combining into one:

Hollie Green

I'm not sure on the Invalid Operation Error as it should allow multiple of the same option to be pulled in. It will just pull it in and only show it once. I did forget a step in the formula try the below that way it will separate your joins.

=IF(ISBLANK([Automation (Corelab)]@row), "", JOIN([Automation (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Cardiometabolic (Corelab)]@row), "", JOIN([Cardiometabolic (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Coagulation, Hematology, Urinanalysis (Corelab)]@row), "", JOIN([Coagulation, Hematology, Urinanalysis (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([CoreLab Reagents (Corelab)]@row), "", JOIN([CoreLab Reagents (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Custom Biotech (Corelab)]@row), "", JOIN([Custom Biotech (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Infectious Disease/RBSS (Corelab)]@row), "", JOIN([Infectious Disease/RBSS (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Oncology (Corelab)]@row), "", JOIN([Oncology (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([SWA Systems (Corelab)]@row), "", JOIN([SWA Systems (Corelab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Blood Screening (Molecular Lab)]@row), "", JOIN([Blood Screening (Molecular Lab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Infectious Diseases (Molecular Lab)]@row), "", JOIN([Infectious Diseases (Molecular Lab)]@row, ", ") + CHAR(10)+

IF(ISBLANK([Oncology & Genetics (Molecular Lab)]@row), "",JOIN( [Oncology & Genetics (Molecular Lab)]@row,", ") + CHAR(10)+

IF(ISBLANK([PCR Systems Workflow & IT (Molecular Lab)]@row), "", JOIN([PCR Systems Workflow & IT (Molecular Lab)]@row ,", ") + CHAR(10)+

IF(ISBLANK([Seq Systems (incl. Informatics) (Molecular Lab)]@row), "",JOIN( [Seq Systems (incl. Informatics) (Molecular Lab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Digital Pathology (Pathology Lab)]@row), "", JOIN([Digital Pathology (Pathology Lab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Oncology Assays (Pathology Lab)]@row), "", JOIN([Oncology Assays (Pathology Lab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Personalized Health Care Solutions (Pathology Lab)]@row), "", JOIN([Personalized Health Care Solutions (Pathology Lab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Systems Workflow & IT (Pathology Lab)]@row), "", JOIN([Systems Workflow & IT (Pathology Lab)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Blood Gas/ Hospital Blood Glucose (Point of Care)]@row), "", JOIN([Blood Gas/ Hospital Blood Glucose (Point of Care)]@row , ", ") + CHAR(10)+

IF(ISBLANK([Clinical Chem/ Immunology (Point of Care)]@row), "", JOIN([Clinical Chem/ Immunology (Point of Care)]@row , ", ") + CHAR(10)+

IF(ISBLANK[LIAT (Point of Care)]@row,"",JOIN([LIAT (Point of Care)]@row,", ")

Benjamin O'Leary

@Hollie Green thank you so much. Still not sure why I'm getting an Invalid Operation error for that one row, but everything else works perfectly.

Hollie Green

Is there a particular column in that row that has something in it that none of the other rows have a value in? If there is I would put a value in that column on another row and see if it gives the same error. It may be something specific to a column in the formula that we are overlooking. It might help narrow it down. Something else to try is if there is blanks that are not blank in any of the other rows try it with a blank in that row.

Benjamin O'Leary

@Hollie Green not sure, might be the issue. However, I just tested the formula by putting in a lot of different options, and only two of the options appear in the column (both options are under the same column)

Hollie Green

Did it give the error on any of your other combinations or just not put the options in there?

Hollie Green

Maybe separate out the formula into separate sections and try each individual formula on that particular row try it as blank and as having an option each time to see if it gives an error message leaving off the final + mark to see if it gives an error. That should at least tell us which part of the formula is causing the error message on that row. It may also fix the other issue of it not pulling in all of the values.

=IF(ISBLANK([Automation (Corelab)]@row), "", JOIN([Automation (Corelab)]@row , ", ") + CHAR(10)

Paul Newcome

Are all of the columns next to each other? If so you can use:

=JOIN(COLLECT([First Column]@row:[Last Column]@row, [First Column]@row:[Last Column]@row, @cell <> ""), CHAR(10))

If not, try going back to your original formula but replace all

", "

with

CHAR(10)

Benjamin O'Leary

@Paul Newcome your formula worked. Thank you so much.