Concatenate multiple header rows into single header row

[matt-gilbert]

I receive data from our clients that has multiple header rows. I need to concatenate these headers into one single row so that I can pivot this data. I know this can be done using a power query however would like the ability to use a formula. Have tried various formulas using COUNT, LOOKUP etc without success. Any help would be appreciated. Sample data attached. Regards, Matt.

 

[Marc L]

'Cause of the merged cells that should be easily achieved under VBA …​

​

 

[mohamed ilyas]

hi

check this.

 

[matt-gilbert]

hi

check this.

Hi Mohamed, wow! I suspected it was possible to do using a formula rather than VBA or power query, but did not realize it would be that complicated. Thank you so much!

 

[Marc L]

Works on recent Excel versions. Older ones need VBA …​

​