File Combination Problem

[vichisov]

I have 80+ files that I need to spliced. Sample attached. The table A9:F64 will always have the same columns, but possible different lengths. A9 will always be the top left corner of the table. I am trying to splice all into one with with C7 from each repeated in column A and then the table to be shifted over one. So outcome would be a new file, combining 80+ files in a folder where the tables are shifted over 1 and Column A is C7 repeated for each table.

I hope this makes sense.

Thank you,

[alansidman]

Consolidating the 80+ files can be easily accomplished using Power Query if all the files are located in the same folder.

HEre is a tutorial https://www.youtube.com/watch?v=a7E29H5ZUmE

[vichisov]

Alan - How would I then import C7 into A position? If I use Power Query to splice everything, is there a smart formula that I can toss into Column A that would find the last Cell in B that has a unique value? The format of C7 is actually: Recipient:**************.

[alansidman]

Perhaps, relocate C7 Value to within your table, ie. G9. In that manner, you have it for each imported table.