Forum Discussion
Combine two lists into one to create a course enrolment csv
Hi
I regularly need to create a simple csv list to enrol users onto courses on our VLE.
The csv needs to have: CourseCode,UserName
I have CourseCode and UserName in separate tables. See example file attached.
How do I combine these into one list please? I'm sure it's very simple (Pivot Table?) but I am a novice Excel user.
---
Excel for Mac 16.52
MacOS Catalina
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.
10 Replies
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?
- Hi trvincent,
it's very simple, just use &, for example =A2&B2, if you want to add ; between CourseCode and UserName it will be like this =A2&";"&B2- Thanks very much for your helpful and swift response.
trvincent See attached, in column H, for the simplest solution.
- Great! Thanks very much for your helpful and swift response. A little embarrassing I didn't think of that!
