Populating Templates Efficiently

[avmejia]

Hello!

I need to fill out more than 100 Excel templates (a separate template per client) that contain information on users and emails. The template has already 3 prepopulated users and emails, so I would need the additional users and emails to be placed right below those 3 rows, so starting in row 8 (see file "Alteryx_Table2"). Furthermore, the source data (see file "Alteryx_Table1") is in one file but split into two tabs. The first tab contains the name of each client and the users and the second tab contains the emails of the users. The users have the employee number in the same cell so this information would need to be removed and each username split into last name and first name to match the format of the template.

I could use TEXT TO COLUMNS to remove the employee number and split the usernames into last name and first name; and then JOIN to add the emails to usernames in the source data but I'm not sure how to make Alteryx populate the templates as I need them.

I fear I'm trying to do too much at once so looking for guidance on how best approach this process.

Thanks for reading!

[Qiu · Accepted solution]

@avmejia 

The challenge here is that how to the alteryx know which template is for which client. 😁
It will be greate if you already havea naming convention that include the Client Name in the File name.

If not, like i am showing below, we need a mapping index.
After that, just sort continue what have done, create a full path and use the option "Preserving formatting" when output the Excels.

[avmejia]

Hi Qiu - I cannot thank you enough for your guidance! Your instructions were crystal clear and the tip on the mapping index extremely helpful. I really appreciate your time :)

[Qiu]

@avmejia 
Thank you for the kind word and its my pleasure to be any help.