Formula to shift column headers into rows

[ansark11]

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?

[protonLeah]

One option is to do Copy/Paste Special+Transpose ---> Dates down rows and Time & age across columns.

[ansark11]

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.

[alansidman]

The following VBA will transpose your data if you first
1. Delete column A
2. Move Time and Age cells down one row.
3. Delete row1 so that all information starts in cell A1

Option Explicit


Sub Normalize()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim i As Long, lr As Long, lrt As Long
    Dim lc As Long
    Set s1 = Sheets("Data")
    Set s2 = Sheets("Result")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False

    For i = 2 To lr
        lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        lrt = s2.Range("D" & Rows.Count).End(xlUp).Row + 1
        s2.Range("A1") = "Date"
        s2.Range("B1") = "Time"
        s2.Range("C1") = "Age"
        s2.Range("D1") = "Data"
        s1.Range(Cells(1, 3), Cells(1, lc)).Copy
        s2.Range("A" & lrt).PasteSpecial xlPasteValues, , , True
        s1.Range("A" & i).Copy s2.Range("B" & lrt)
        s1.Range("B" & i).Copy s2.Range("C" & lrt)
        s1.Range(Cells(i, 3), Cells(i, lc)).Copy
        s2.Range("D" & lrt).PasteSpecial xlPasteValues, , , True
        Application.CutCopyMode = False
    Next i

    lrt = s2.Range("A" & Rows.Count).End(xlUp).Row
    For i = 3 To lrt
        If s2.Range("B" & i) = "" Then
            s2.Range("B" & i) = s2.Range("B" & i - 1)
        End If
        If s2.Range("C" & i) = "" Then
            s2.Range("C" & i) = s2.Range("C" & i - 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

[ansark11]

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!

[azumi]

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]

Here is the file with the Macro executed. I forgot to mention, I renamed Sheet1 and Sheet2 to Data and Result respectively.

[ansark11]

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

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.