Importing columns from multiple workbooks into next available column

[Clairekun]

Hello,

Although I assume this is a fairly regular inquiry, I have been unable to find it in the forum, please excuse me if the thread already exists. And sorry if the post is too long, I would like to express myself as clearly as possible.

I have many excel files which I converted from PDF and later polished by combining different macros I found online which would most likely make you cringe (since I have very limited knowledge of VBA and I'm pretty sure the macro, altough working, is a complete mess).

As a result, the excel files (which correspond to different experimental samples, in case it is relevant somehow) have two columns each, formatted as tables, where Column 1 is text and Column 2 is numerical (with some blank cells). Column 1 is the same for each file, but column 2 differs. Also, the sheet where data is stored is renamed to the sample name (relevant later).

What I would need is to have a workbook where Column 1 is imported from any of the files (they're all the same, so it doesn't matter which), where the following columns are the imported Column 2 from the rest of files. Ideally, I would like to choose the files manually; although I could copy and paste them into the same folder, that is not their original location, but this is not mandatory.

Since all Column 2 have the same type of data, the headers are also the same; to know what sample they come from, the imported column headers in the master file would have to be renamed to either the source workbook or worksheet name. I'll try to explain it graphically:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Text[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]cc[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample 1 (worksheet label)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Text[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]cc[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample 2 (worksheet label)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Result:

[TABLE="width: 300"]
<tbody>[TR]
[TD]Text[/TD]
[TD]Sample 1[/TD]
[TD]Sample 2[/TD]
[TD]Sample 3, etc.[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]12[/TD]
[TD]54[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]14[/TD]
[TD]85[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]cc[/TD]
[TD]16[/TD]
[TD]96[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]