I have a template for one shop, that I need to copy but change only the first column, based on list of shops I can provide. The number of shops and number of items can change multiple times a week. So I end up with 10k number of rows, and at the moment I just copypaste manually and change shop name every x number of rows. I would like to automate this process, so I can make a template for one shop, provide names of other shops, and it will copy the rows and columns of first shop to all other shops.

Now, I have another x number of shops, like beta, charlie, delta, echo, etc and I need the sheet to look like this

Because there can be 40 shops with 150 items each, that is thousands of rows I do manually.

Is there a way Excel can do this more efficiently?

CodePudding user response：

I would do this in PowerQuery, not VBA. Example file attached. Just update the template and shoplist and hit the Data > Refresh all button. The result will be in the Final sheet.

You can add as many columns/lines as you want in the template but keep the "shop" column blank. In the shop list you can only add rows and you must keep the merge column blank.

Merge shoplist based on template

If you want to see the code used go to menu Data > Queries and connections > right click any query in the new pane and choose edit.