Copy row to another sheet by COUNT until COUNT = 0, or other Parse option
Hi folks,
I've got rows that collect courses for students into the COURSES REQUIRED column separated by commas.
I've already setup a formula to count the number of courses found there
What I'd like to achieve is that for each student/row containing multiple classes in their required list ... parse that students row such that if there are 5 courses required the row is copied 5 times to another sheet well call Registration Sheet, copy once for each course taking with it 1 course from that list each time using the comma delimiter to help
Below it would go from this, Jane Doe has multiple courses bundled in COURSES REQUIRED
to individual rows like this
Course Req | Employee
_______________________________
EP0061-125 | Doe, Jane
INW206-22:25 | Doe, Jane
MC0037-22:25 | Doe, Jane
MC0047-22:25 | Doe, Jane
MC0056-22:25 | Doe, Jane
I've tried using automation and and increment helper as you can see but my solution triggered an infinite loop warning even though I had it set to trigger only until the Count Cycle reached zero... so it was not infinite but SS didnt know that of course.
Answers
-
Is it possible to, instead of compiling the information in one location, use the source location to break down the information into the destination? So instead of using movements workflows use blanket formulas to represent what you want?
-
@L_123 - If not sure how to achieve that... maybe a join w/ index match??
here are the Source sheets
COURSES SHEET
EMPLOYEES SHEET
Essentially... for every employee, I need an individual row for the training where their Position name (Hand of the King, for ex) is marked as REQUIRED COURSES FOR THIS POSITION... so for the Hand...
2 rows containing all the Employee columns, plus a Column for Course Training ID found, i.e.
FINAL SHEET
row1 PSO0172:84 someones name 65224 GAME OF THRONES XYZ2 z_Hand fo the King
row2 PSO0175 someones name 65224 GAME OF THRONES XYZ2 z_Hand fo the King
If this is easier to do, i'll take it
-
I think you would be better off with an index(Collect(),countif()) with a helper column to figure out how many results there needs to be would be the way to go. I can try to write something up tomorrow
-
@L_123 -thanks ... i've been racking my head. I'll look for your suggested formula tomorrow
-
@Paul Newcome You might like this one if you have time tonight. If not i'll figure it out in the am
-
What is the maximum number of courses you can have listed in a single entry?
-
total unique courses in the list is 150
but each student may only need a portion of that list, varying by student
-
What is the max number a single student could need?
-
The max per student is 20
-
hi guys just circling back on this one :)
-
@Jennifer Mahoney Hi Jennifer, I've been coming back to this one for a couple days, and while I have a couple solutions, they are fairly convoluted. I don't know how much time you want to put into making this automated.
@Andrée Starå maybe you have a good idea to do this in a simple way
-
@L_123 and @Andrée Starå I would put in the time to put this into an automated action.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!