Re-Arranging the row data in columns

Question

I have data in excel in following format

Resource     2/2/2013   2/3/2013  2/4/2013
  Name1         9          9         9  
  Name2         9          9         9  
  Name3         9          9         9  

I have to convert the above data into something like this:

Resource  Date        Hours
Name1     2/2/2013      9
Name1     2/3/2013      9
Name1     2/4/2013      9
Name2     2/2/2013      9
Name2     2/3/2013      9
Name2     2/4/2013      9
Name3     2/2/2013      9
Name3     2/3/2013      9
Name3     2/4/2013      9

Is there any function in excel that can do that. I could find only the row to columns function that didn't help me as it will just transpose the data and not create multiple entries like above.

What could be the best way to do this even through VBA.

Answer 1

I was wondering if it is possible make without VBA and it seems that it is. But with some presumption, specifically that the area you are transformin is rectangular.

Then you could use QUOTIENT a MOD function (it would be possible to merge "helper columns C-E" together but for cleaner explanation I show them).

In A9 I have a value with number of columns (it could be get by another function) - just to be a bit generic.

Then I use INDIRECT() function in this manner:

• cell G9: =INDIRECT("R"&2+D9&"C1";FALSE)
• cell H9: =INDIRECT("R1C"&2+E9;FALSE)
• cell I9: =INDIRECT("R"&2+D9&"C"&2+E9;FALSE)

And then just drag it down.

Answer 2

Here is a VBA solution:

Sub Example()
    Dim Resources() As String
    Dim rng As Range
    Dim row As Long
    Dim col As Long
    Dim x As Long

    ReDim Resources(1 To (ActiveSheet.UsedRange.Rows.Count - 1) * (ActiveSheet.UsedRange.Columns.Count - 1), 1 To 3)

    'Change this to the source sheet
    Sheets("Sheet1").Select

    'Read data into an array
    For row = 2 To ActiveSheet.UsedRange.Rows.Count
        For col = 2 To ActiveSheet.UsedRange.Columns.Count
            x = x + 1
            Resources(x, 1) = Cells(row, 1).Value    ' Get name
            Resources(x, 2) = Cells(1, col).Value    ' Get date
            Resources(x, 3) = Cells(row, col).Value  ' Get value
        Next
    Next

    'Change this to the destination sheet
    Sheets("Sheet2").Select

    'Write data to sheet
    Range(Cells(1, 1), Cells(UBound(Resources), UBound(Resources, 2))).Value = Resources

    'Insert column headers
    Rows(1).Insert
    Range("A1:C1").Value = Array("Resource", "Date", "Value")

    'Set strings to values
    Set rng = Range(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))
    rng.Value = rng.Value
End Sub

Original:

Result: