How to Consolidate columns by their Title

[Gocification]

Hey

I have these excel files in the same format as the example sheet that I need to consolidate by their column headings but the number of different headings and rows differ per sheet so instead of there being 4 types of headings to consolidate over 16 columns there could be 400 spread over 1000 columns. any help would be much appreciated as i have no idea how to achieve this.

Thanks

 

[Luke M]

Hello Gocification, and welcome to the forum!

Using your example layout as a start, in cell W2, you can put formula:
=SUMIF($C$1:$Q$1,W$1,$C2:$Q2)

You would then copy right and down as needed.
In a larger example, as long as you know what the final column labels will be (you can use XL's Data - Remove Duplicates) tool to quickly get a unique list), and still get the combined answer.

 

[p45cal]

Another idea, see attached.

 

[Luke M]

Nice job p45cal. Especially enjoyed the names of your macros.

 

[Gocification]

Thanks for the help much appreciated