Separate multiple cells by delimiter into multiple rows
I am trying to develop a WFH schedule tool. I have a form that asks managers to check boxes for Week X starting on X Monday Date which days their team will be in the office.
Then I have the formula to convert the check marks into TEAM NAME Date + ","...
On another sheet, I'm trying to convert the text created in COMBINE into multiple rows. I would like it to look like this so I can return a Calendar that shows the dates each team in the office.
I believe I'll need to use MID
, FIND
and SUBSTITUTE
in my formula (not 100% sure how but there's more info out there on that), but I can't figure out how to return 3 rows of COMBINE1 and 2 rows of COMBINE2 relevant to how many dates are present in it, and then how to provide unique results for each return.
Then I'd have to split by a different delimiter probably into TEAM NAME and DATE.
My final product of the calendar view should look like this.
Thank you for your help! I can share sheets if helpful.
Best Answer
-
I have an idea on how to do this, but bear with me...it has a few helper columns, workflows, and formulas.
In your current sheet, create 5 Workflows - one for each Checkbox - to Copy the row over to your second sheet.
These would be very simple, but this would create multiple rows based on the number of checkboxes used in your form. Here's an example of one:
So this means an entry like this:
Turns into two rows in the destination, like this:
(We'll get to the formula in the Date column in a minute.)
Destination Sheet Set-Up
The first thing we'll set up is the Rank for each row to find out how many came in through the automations. These are the 3 columns to the left of the blue columns.
3 Columns:
- Auto-Number System Column (I called Row ID)
- Row Number column using a formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
- Rank column using a formula based on the Row Number column:
=RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Team Name]:[Team Name], [Team Name]@row, [Week Start]:[Week Start], [Week Start]@row), 1)
Now that we can identify what copied row is what... we need to generate the respective date in the blue Date column.
I've used a Nested IF statement to do this, checking the Rank column number and then looking against all the possible checkbox columns for that row to return the correct date. I have to admit I'm certain there's a cleaner way of writing this formula, but this is just how my brain is working tonight:
=IF(Rank@row = 1, IF(M@row = 1, [Week Start]@row, IF(T@row = 1, [Week Start]@row + 1, IF(W@row = 1, [Week Start]@row + 2, IF(Th@row = 1, [Week Start]@row + 3, IF(F@row = 1, [Week Start]@row + 4))))), IF(Rank@row = 2, IF(AND(M@row = 1, T@row = 1), [Week Start]@row + 1, IF(AND(COUNTIF(M@row:T@row, 1) = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 1, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 1, F@row = 1), [Week Start]@row + 4)))), IF(Rank@row = 3, IF(AND(M@row = 1, T@row = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 2, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 2, F@row = 1), [Week Start]@row + 4))), IF(Rank@row = 4, IF(COUNTIF(M@row:Th@row, 1) = 4, [Week Start]@row + 3, [Week Start]@row + 4), [Week Start]@row + 4))))
Then you can hide ALL of the columns except the two blue ones in your Calendar sheet!
I whizzed through that, so if you need me to break down how the last Nested IF works, let me know and I'd be happy to clarify further.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
I have an idea on how to do this, but bear with me...it has a few helper columns, workflows, and formulas.
In your current sheet, create 5 Workflows - one for each Checkbox - to Copy the row over to your second sheet.
These would be very simple, but this would create multiple rows based on the number of checkboxes used in your form. Here's an example of one:
So this means an entry like this:
Turns into two rows in the destination, like this:
(We'll get to the formula in the Date column in a minute.)
Destination Sheet Set-Up
The first thing we'll set up is the Rank for each row to find out how many came in through the automations. These are the 3 columns to the left of the blue columns.
3 Columns:
- Auto-Number System Column (I called Row ID)
- Row Number column using a formula: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
- Rank column using a formula based on the Row Number column:
=RANKEQ([Row Number]@row, COLLECT([Row Number]:[Row Number], [Team Name]:[Team Name], [Team Name]@row, [Week Start]:[Week Start], [Week Start]@row), 1)
Now that we can identify what copied row is what... we need to generate the respective date in the blue Date column.
I've used a Nested IF statement to do this, checking the Rank column number and then looking against all the possible checkbox columns for that row to return the correct date. I have to admit I'm certain there's a cleaner way of writing this formula, but this is just how my brain is working tonight:
=IF(Rank@row = 1, IF(M@row = 1, [Week Start]@row, IF(T@row = 1, [Week Start]@row + 1, IF(W@row = 1, [Week Start]@row + 2, IF(Th@row = 1, [Week Start]@row + 3, IF(F@row = 1, [Week Start]@row + 4))))), IF(Rank@row = 2, IF(AND(M@row = 1, T@row = 1), [Week Start]@row + 1, IF(AND(COUNTIF(M@row:T@row, 1) = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 1, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 1, F@row = 1), [Week Start]@row + 4)))), IF(Rank@row = 3, IF(AND(M@row = 1, T@row = 1, W@row = 1), [Week Start]@row + 2, IF(AND(COUNTIF(M@row:W@row, 1) = 2, Th@row = 1), [Week Start]@row + 3, IF(AND(COUNTIF(M@row:Th@row, 1) = 2, F@row = 1), [Week Start]@row + 4))), IF(Rank@row = 4, IF(COUNTIF(M@row:Th@row, 1) = 4, [Week Start]@row + 3, [Week Start]@row + 4), [Week Start]@row + 4))))
Then you can hide ALL of the columns except the two blue ones in your Calendar sheet!
I whizzed through that, so if you need me to break down how the last Nested IF works, let me know and I'd be happy to clarify further.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. That was brilliant! I understood it perfectly and your help is SO appreciated! I've been banging my head against the wall about this for a week plus, unable to get my mind around a solution! You saved me!
-
Oh good! I'm glad I could help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!