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.
Thanks for the replies. I just realised this isn't quite the solution I needed. I probably wasn't clear enough in my explanation:
I only have the two data sets CourseID and UserName - I need to generate the combined list of the two, as demonstrated in the third (green) table in the attached example. I can then save this as a CSV.
Anyone have any ideas how to create this combination please? I'm sure it is simple when you know how!
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.