Transpose and Fill

[Alberto Cohen]

In the attached spreadsheet, I put a section of the 300 survey responses I received. To post-process survey results I have to convert data shown in the first Sheet to a layout similar to what I introduced in the second Sheet.
The core task involves tranposing the several question columns into rows and repeating each respondent name besides each question. (It is easier to understand just by looking at the example attached.)
I can´t figure out how to achieve and automate this so that I can proceed to analyze data with pivot tables and statistical packages.

 

[NARAYANK991]

Hi Albert ,

If the answers will always be equal to the number of questions , then this can be done using formulae. Check the uploaded file.

Narayan

 

[Smallman]

Here is a vba solution to go with the problem.

Option Explicit
Option Base 1
Sub test()
Dim ar As Variant
Dim var()
Dim j As Integer
Dim i As Integer
Dim Ub1 As Long
Dim n As Integer
 
ar = Range("A1").CurrentRegion
Ub1 = UBound(ar, 1) 'Length of the Array
ReDim var(Ub1 * (UBound(ar, 1) - 1), 3)
 
    For j = 2 To Ub1
        For i = 1 To 4
            n = n + 1
            var(n, 1) = ar(j, 1) 'Name
            var(n, 2) = ar(1, i + 1) 'Question eg 1,2,3,4
            var(n, 3) = ar(j, i + 1) 'Desc
        Next i
    Next j
Sheet2.[a2].Resize(Ub1 * (UBound(ar, 1) - 1), 3) = var
End Sub

File attached to prove workings.

Take care

Smallman

 

[Alberto Cohen]

Hi Albert ,

If the answers will always be equal to the number of questions , then this can be done using formulae. Check the uploaded file.

Narayan

Success! I deeply appreciate your taking the time and patience to teach me how to achieve it.

 

[Alberto Cohen]

Here is a vba solution to go with the problem.

Option Explicit
Option Base 1
Sub test()
Dim ar As Variant
Dim var()
Dim j As Integer
Dim i As Integer
Dim Ub1 As Long
Dim n As Integer

ar = Range("A1").CurrentRegion
Ub1 = UBound(ar, 1) 'Length of the Array
ReDim var(Ub1 * (UBound(ar, 1) - 1), 3)

    For j = 2 To Ub1
        For i = 1 To 4
            n = n + 1
            var(n, 1) = ar(j, 1) 'Name
            var(n, 2) = ar(1, i + 1) 'Question eg 1,2,3,4
            var(n, 3) = ar(j, i + 1) 'Desc
        Next i
    Next j
Sheet2.[a2].Resize(Ub1 * (UBound(ar, 1) - 1), 3) = var
End Sub

File attached to prove workings.

Take care

Smallman

It also worked fine! Again, you helped and taught me valuable lessons. Thank you!

 

[Smallman]

Hi Alberto

Narayan's is the more elegant solution of the two. Thanks for the comments though.

See you next time

Smallman