I have multiple files with 1-2 sheets. Sheet1 has a header type information area that I want to pull some info into a single sheet as a summary.
There are merged cells in the mix & some odd locating to make the report fit on 1 page. Some of it is Column 1=heading, column2=data & a few are Row1=heading, Row2=data, underneath the above columns.

I know how to copy the data to a new sheet via query-at least from within the same workbook but multiple files doesn't look that hard - but I'm stuck on formatting it to a row/column setup that I can put the data from each file into it's own row (and exclude data I don't want, but I could pull it all & hide columns if necessary).

I've got a scrubbed example workbook if anybody wants to see the exact structure, but basically: ( | is column divider)

1|data1
2|data2
3
data3

is what is throwing me off - how do I make it:
File1|data1|data2|data3
File2|data1|data2|data3

Without seeing it, I'm having a hard time with what you described. I believe it sounds to big to copy and transpose the paste.

If it isn't confidential data, you can email me the file and I can see what I can do this afternoon. I should have time after 12 noon AZ time.

I should have known better than to do this on a holiday Friday when I thought nobody would bother me. Started trying things, got frustrated, then posted this & all hell broke loose....

Beancooker, you have mail.

Andy,
Email reply in your inbox. I hope that helps. Sorry I’m not near a PC with excel. I think match and index will be the solution.