Excel Data Formatting

Question

I am struggling with formatting the following table. The goal is to go from table 1 to table 2. The image below presents a sample from a large dataset. How do you transpose table 1 and converted to table 2? Thank you.

score 1 · Answer 1 · answered Oct 07 '16 at 04:30

I think what you want is an unpivot. It's actually native in Excel 2013, but for versions prior you have to hack your own version. Here's a really basic example based on some of your sample data. If you make minor adjustments to accommodate your actual range locations, this should get you 90% of the way there.

Sub Unpivot()

  Dim headerRow, row, newRow, col As Integer
  Dim ws1, ws2 As Worksheet

  Set ws1 = Sheets(1)
  Set ws2 = Sheets(2)

  headerRow = 2
  row = 4
  newRow = 4

  While ws1.Cells(row, 1).Value <> ""
    col = 5
    While (ws1.Cells(headerRow, col) <> "")
      ws2.Range("A" & newRow & ":D" & newRow).Value = _
          ws1.Range("A" & row & ":D" & row).Value
      ws2.Cells(newRow, 5).Value = ws1.Cells(headerRow, col).Value
      ws2.Cells(newRow, 6).Value = ws1.Cells(row, col).Value
      newRow = newRow + 1
      col = col + 1
    Wend

    row = row + 1
  Wend

End Sub

Sample input, with rows and columns:

Sample output:

score 0 · Accepted Answer · edited May 23 '17 at 12:19

0

You're not transposing the data (flips the data along the X= -y diagonal); you're converting the two dimensional range into a single dimensional range.

Additionally, this was already answered here:

edited May 23 '17 at 12:19

Community

1
1

answered Oct 06 '16 at 21:46

KareemElashmawy

251
5
16

I appreciate your response, could you please demonstrate that on my example. I am a beginner and tried the formula in the link but I didn't work. – Moe.A Oct 06 '16 at 22:20

Excel Data Formatting

2 Answers2