Help me merge data in a specific way

[giokinkla]

So i have two excel files, one with the list of files in folders and second with list of the same folders.

My goal is to merge this two data so that each folder has list of the files in horizontal cell like this

What i've tried: IF formula with CONCATENATE, creating a bot with macro recorder, googling exact solution. no luck in any of them (except for a bot, i was somewhat close to making it work but the data is 11,000 rows and it will make a mistake the way i had created it)

[#2 · Accepted solution]

@giokinkla Attached a simplified example of how I would do this with PowerQuery. The example uses two small tables on the same sheet, rather than two separate files, but the principle is the same. If you are new to PQ, it will take some time to get used to, but it's worth learning, especially when you intend to perform such tasks frequently on large data sets.

The link below would be a good starting point.

https://exceloffthegrid.com/power-query-introduction/ 

[#3]

@Riny_van_Eekelen Thanks! for now it's too complicated for me to understand it but the directions are great! Thanks again