you have to data stacking for this. After preparing stack data (response level data) you will be able to see data separately for each team in different row.



you have to do this using SPSS/R/EXCEL and the importing responses back to new survey. doing so your one row will be divided in to 20 rows and each row will contain data for each time. like row1 for team 1....so on.



so for example like if you have Q1 running for 20 teams currebtly you have variables like Q1_1, Q1_2, Q1_3.....Q1_20.

After Stacking your data will have one variable for Q1 and Teamnames.

---

Thank you! Your answer led me to find a macro online that is clearly heading in the right direction, but it is not reading the groups correctly. (It is reading them as 1,2,3,1,2,3 when the data is 1,1,1,2,2,2,3,3,3) I may be able to get some help at the office to review/revise the macro in VBA, but do you by chance know of a good resource for sample macros to do this? We don't have SPSS or R. Here is the macro that is almost right, but not quite.



Sub ReOrganize2()

'JBeaucaire (rewritten 3/21/2014)

'Turns row data into columnar data, groups of data merged down together

'Option to copy the header down as a new data cell from each group created

Dim wsRAW As Worksheet, wsNEW As Worksheet, ClassNm As Boolean

Dim NR As Long, LR As Long, Rw As Long, FirstCol As Long, ColGrps As Long, LastCol As Long, Col As Long



'Confirm raw data is the activesheet onscreen

If MsgBox("Reorganize the activesheet?", vbYesNo, "Proceed?") = vbNo Then Exit Sub



'User indicates which column is the first column to parse down, columns to left will be duplicated

FirstCol = Application.InputBox("What is the first column number to parse out?" _

& vbLf & "(B=2, C=3, etc.)" & vbLf & "Columns to the left will all be duplicated.", "First Column Number", 4, Type:=1)

If FirstCol < 2 Then Exit Sub



'User indicates how many columns to parse down in groups

ColGrps = Application.InputBox("How many columns per group to parse out?", "Columns Per Group", 2, Type:=1)

If ColGrps = 0 Then Exit Sub



'Option to put the title from the first column of each group into each new data row as data

If MsgBox("Copy the first column name title for each group down into each new row?", vbYesNo) = vbYes Then ClassNm = True



Set wsRAW = ActiveSheet

Set wsNEW = Sheets.Add



'Create titles on new sheet

If ClassNm = True Then

wsNEW.Range("A1").Resize(, FirstCol - 1).Value = wsRAW.Range("A1").Resize(, FirstCol - 1).Value

wsNEW.Cells(1, FirstCol).Resize(, 2).Value = [{"Class Name","Status"}]

wsNEW.Cells(1, FirstCol + 2).Resize(, ColGrps - 1).Value = wsRAW.Cells(1, FirstCol + 1).Resize(, ColGrps - 1).Value

Else

wsNEW.Range("A1").Resize(, FirstCol + ColGrps - 1).Value = wsRAW.Range("A1").Resize(, FirstCol + ColGrps - 1).Value

End If



'Figure out how many rows and columns of data need to be processed

LR = wsRAW.Range("A" & Rows.Count).End(xlUp).Row

LastCol = wsRAW.Cells(1, Columns.Count).End(xlToLeft).Column



'Set the first row to enter new data on new sheet

NR = 2



'process one raw data row at a time

For Rw = 2 To LR

'process each group of column

For Col = FirstCol To LastCol Step ColGrps

If wsRAW.Cells(Rw, Col) <> "" Then

'first copy the initial columns that will exist on all the parsed out rows

wsNEW.Range("A" & NR).Resize(, FirstCol - 1).Value = wsRAW.Range("A" & Rw).Resize(, FirstCol - 1).Value

'if the first column name is being copied down, do that, then copy the remaining cells of the group down

If ClassNm = True Then

wsNEW.Cells(NR, FirstCol).Value = wsRAW.Cells(1, Col).Value

wsNEW.Cells(NR, FirstCol + 1).Resize(, ColGrps).Value = wsRAW.Cells(Rw, Col).Resize(, ColGrps).Value

'if first column name is not being copied down, copy down all the cells of the group

Else

wsNEW.Cells(NR, FirstCol).Resize(, ColGrps).Value = wsRAW.Cells(Rw, Col).Resize(, ColGrps).Value

End If

'set the next empty row to copy into

NR = NR + 1

End If

Next Col

Next Rw

'cleanup result

wsNEW.Columns.AutoFit

wsRAW.Range("A2").Resize(, FirstCol + ColGrps - 1).Copy

wsNEW.Range("A1").Resize(NR, FirstCol + ColGrps - 1).PasteSpecial xlPasteFormats

wsNEW.Range("A2").Select

ActiveWindow.FreezePanes = True

End Sub

---

I have SPSS license, I can do it for you.

---

With SPSS it is much easier to do by writting vartocases syntax.

---

That would be great, but we will need to be able to re-parse the data frequently, so I think excel will work best for us. Thank you though!

---