How to split text to support reporting?
Hi,
I am bumping into a design / Smartsheet limitation issue. I use an request inventory sheet to track client change requests. CR are submitted via a form. One of the field helps tracking the category of the CR (business feature grouper). I had to concatenate 2 types of data to workaround the form and the fact that I cannot have a conditional menu. Example: if 01) core claim processing is selected, then sub menu should display only A,B,C. See information and table below for more details.
What I'd like to do now is report only on the business feature grouper, not the sub-groupers (so 01) Core Claim Processing, 02) Payroll Processing, 03) Claim Correspondence, etc.). I am manually capturing the information in dedicated column that is hidden and that is used in a report.
How can I either use a formula to split the grouper field and capture only the first part of it?
OR how can I redesign my form to capture the grouper / sub grouper in an easy way?
Current drop down menu, to support form submission:
01) Core Claim Processing 10) Leave Plan Configuration (Fed FMLA, State FMLA, ADA, Military, and all flavors of company leaves)
01) Core Claim Processing 20) Disability Plan Design(STD, LTD, PFL, STAT, etc.)
01) Core Claim Processing 30) Claim Workflow
01) Core Claim Processing 40) Claim Tasking
01) Core Claim Processing 50) Claim Calculations
01) Core Claim Processing 60) Intake Scripts
01) Core Claim Processing 98) TBD
01) Core Claim Processing 99) Other
02) Payroll Processing 10) Benefit Calculations
02) Payroll Processing 98) TBD
02) Payroll Processing 99) Other
03) Claim Correspondence 10) Letters
03) Claim Correspondence 20) Emails
03) Claim Correspondence 30) Text
03) Claim Correspondence 40) Forms
03) Claim Correspondence 98) TBD
03) Claim Correspondence 99) Other
04) Inbound File Feed Integration From ER or Agent 10) SSO Setup
04) Inbound File Feed Integration From ER or Agent 20) Roster File
04) Inbound File Feed Integration From ER or Agent 30) API Integration
04) Inbound File Feed Integration From ER or Agent 40) Conversion of Takeover claims
04) Inbound File Feed Integration From ER or Agent 98) TBD
04) Inbound File Feed Integration From ER or Agent 99) Other
05) Outbound File Feed Integration To ER or Agent 10) Time Keeping File Feed
05) Outbound File Feed Integration To ER or Agent 20) Leave Status File Feed
05) Outbound File Feed Integration To ER or Agent 30) Data Warehouse File Feeds
05) Outbound File Feed Integration To ER or Agent 98) TBD
05) Outbound File Feed Integration To ER or Agent 99) Other
06) Digital Tools 10) Next Self Service Portal
06) Digital Tools 20) Self-Service Reporting
06) Digital Tools 98) TBD
06) Digital Tools 99) Other
07) External Vendor Integration 10) Cigna Better Integration
07) External Vendor Integration 98) TBD
07) External Vendor Integration 99) Other
08) Telephony / IVR Integration
09) Information Security
10) Partner Core Integrations (Carrier) 10) CIGNA
10) Partner Core Integrations (Carrier) 20) GLIC
10) Partner Core Integrations (Carrier) 30 )GLIC
10) Partner Core Integrations (Carrier) 40) Guardian
10) Partner Core Integrations (Carrier) 50) Co-Source
10) Partner Core Integrations (Carrier) 98) TBD
10) Partner Core Integrations (Carrier) 99) Other
Mapping table:
Answers
-
Are you able to provide a screenshot of the portion you are wanting to automate?
-
Here you go. The column "Request Business Feature Grouper" is the one populated via form. The business feature grouper column is the one I manually update when a new request is submitted to use in a report.
-
Create (or if you have already a) sheet that just has the list of Business Feature Groupers (01) Core Claim Processing, 02) Payroll Processing, etc.).
Then in your Business Feature Grouper column you would use the following formula:
=IFERROR(JOIN(COLLECT({Business Feature Grouper Range}, {Business Feature Grouper Range},CONTAINS(@cell,[Request Business Feature Grouper]@row)), CHAR(10)), "")
-
The formula below returns an incorrect argument set. I am not too sure I understand how it works to troubleshoot. Any thoughts?
=IFERROR(JOIN(COLLECT({Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row)), CHAR(10)), "")
-
You will need to repeat that first range a second time within the COLLECT function:
=IFERROR(JOIN(COLLECT({Grouper Mapping Business grouper mapping}, {Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row)), CHAR(10)), "")
-
Here is what the formula returns.
Here is a snapshot of the mapping table - do I need to change anything in the mapping?
-
Thanks @Paul Newcome for the correction :)
The list of 'Feature's should only have each 'Feature' once.
Alternatively you can make a duplicate column and exclude it in your formula:
=IFERROR(JOIN(COLLECT({Grouper Mapping Business grouper mapping}, {Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row),{Duplicate Range},@cell <>""1), CHAR(10)), "")
-
Updating the mapping table fixed the issue with the duplicates.
I have one more issue with one of the grouper: the formula does not work with 2 of the cells. I am not sure why. That's the only 2 cells that returns blank over 200.
Business Feature Grouper
-
Lets change the formula to not use the duplicate column and instead add the DISTINCT function.
=IFERROR(JOIN(DISTINCT(COLLECT({Grouper Mapping Business grouper mapping}, {Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row))), CHAR(10)), "")
It looks like you do have some things spelled differently:
e.g. 'Claim Correspondence' vs 'Claim Correspondance' (e vs a)
I would go through and make sure they are all spelled the same...
-
Good catch, that fixed the issue. I did not see the typo :)
Help Article Resources
Categories
Check out the Formula Handbook template!
