Forum Discussion
Combine two lists into one to create a course enrolment csv
Easiest way id with Power Query, but since you are on Mac that doesn't work. Half-manual way
- create another table with only one empty row in it
add two formulas in columns of this row
=INDEX(Table1[CourseCode], INT( (ROW()-ROW(Table5[[#Headers],[CourceCode]])-1)/ROWS(Table2))+1)and
=INDEX(Table2[UserName], MOD( ROW()-ROW(Table5[[#Headers],[UserName]])-1, ROWS(Table2))+1)Number to the right calculates which will be last row. In our case enter H5:I88 into name box, this range will be select.
Ctrl+D. Table will be expanded and filled.
Easiest way id with Power Query, but since you are on Mac that doesn't work. Half-manual way
- create another table with only one empty row in it
add two formulas in columns of this row
=INDEX(Table1[CourseCode], INT( (ROW()-ROW(Table5[[#Headers],[CourceCode]])-1)/ROWS(Table2))+1)and
=INDEX(Table2[UserName], MOD( ROW()-ROW(Table5[[#Headers],[UserName]])-1, ROWS(Table2))+1)Number to the right calculates which will be last row. In our case enter H5:I88 into name box, this range will be select.
Ctrl+D. Table will be expanded and filled.
Thank you very much for this. That is really helpful. It works - almost! It only seems to create the first 9 usernames/rows before going onto the next CourseID. There should be 125. I attach the results of my calculation. Do you know why this is happening please?
Part of your formulas is
ROWS('https://unibrightonac.sharepoint.com/sites/Group-BSMSLearningTechnologists/Shared Documents/Collaborative Work/My Studies Enrolments etc/2021-2022 enrolments/module areas 2021-2022.xlsx'!Table2[#Data])I'm not sure is that real table or you copy/paste with link. In attached file is corrected variant for the tables in file.
Ah yes! Thanks for pointing that out. And thank you for your correction. That has worked!
Thanks again. That has really made a difference to my workflow.
trvincent , you are welcome, glad it helped