Issues With Extra Commas When Joining Values of Multiple Columns Into One
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.
Answers
-
Is it possible these random commas are already in the cells you are pulling in?
-
@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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
-
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,", ")
-
@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:
-
@Hollie Green also, the different options are combining into one:
-
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,", ")
-
@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.
-
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.
-
@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)
-
Did it give the error on any of your other combinations or just not put the options in there?
-
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)
-
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)
-
@Paul Newcome your formula worked. Thank you so much.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!