Formula to shift column headers into rows

Excel.PNG

Hey guys, I'm pulling raw data from Adobe Analytics and by default it displays the data in the form of a pivot table. As a result, all of the date values are displayed as column headers as opposed to being categorized in a row.

I want to change the layout so that the date values show up in the rows under one column (ie. A "date" column). The issue I'm having is that each date value is associated with multiple data points (ex. the date "2018/05/20" is associated with all the data listed underneath it). Is there any way for me to shift the dates into rows while keeping them linked to their associated data points?

Thanks for the reply.

I'm looking to keep the layout of the table exactly the same but just shift the dates into rows while keeping them linked to their associated data points. Transposing the dates into rows puts me in another problem where I have multiple headers (ie. time intervals as one header and age groups as another header). This causes issues when analyzing the data (for example if I wanted to look at the number of individuals between the ages of 18-24 who used the website for 1-5 minutes over the last two weeks, I can't do that since Excel will only capture one row of headers).

Sorry I should have clarified this in my original post.

Thanks alansidman!

I followed all your steps but when I try to run the VBA code I get an error saying "Subscript out of range"? My VBA knowledge is very limited so I'm probably making a silly error.

Btw, I'm interested to see what you got on your end when you ran the code. Mind posting a screenshot of it?

Thanks!

For a Date, put this on B27 and copied down
=IFERROR(INDEX($D$2:$Q$2,ROWS($A$1:A1)),"")

For Time, put this on C25 and copied cross
=INDEX($B$3:$B$23,COLUMNS($A$1:A1))

For Age, put this on C26 and copied cross
=INDEX($C$3:$C$23,COLUMNS($A$1:A2))

For Value, put this on C27 and entered as array formula and then copied down and cross
=IFERROR(INDEX($D$3:$Q$23,0,MATCH($B27,$D$2:$Q$2,0)) INDEX($D$3:$Q$23,MATCH(1,($B$3:$B$23=C$25)*($C$3:$C$23=C$26),0),0),"")

alansidman that macro did the trick, thanks a million! I feel it's time for me to start learning VBA, super useful in situations like this.

Thanks everyone for your help with this