Copy row to another sheet by COUNT until COUNT = 0, or other Parse option

Jennifer Mahoney

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.

L_123

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?

Jennifer Mahoney

@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

L_123

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

Jennifer Mahoney

@L_123 -thanks ... i've been racking my head. I'll look for your suggested formula tomorrow

L_123

@Paul Newcome You might like this one if you have time tonight. If not i'll figure it out in the am

Paul Newcome

What is the maximum number of courses you can have listed in a single entry?

Jennifer Mahoney

@Paul Newcome

total unique courses in the list is 150

but each student may only need a portion of that list, varying by student

Paul Newcome

What is the max number a single student could need?

Jennifer Mahoney

@Paul Newcome

The max per student is 20

Jennifer Mahoney

@Paul Newcome @L_123

hi guys just circling back on this one :)

L_123

@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

Casie

@L_123 and @Andrée Starå I would put in the time to put this into an automated action.