TRANSPOSING Multiple Rows of Data

[Learn_Excel]

Hi,

I have a table like this in Sheet1:


<TABLE style="WIDTH: 291pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=387 border=0><COLGROUP><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 117pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=156 height=20>Question</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 110pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=146>response</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=85>Employee ID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Willson David</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100738</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>City, State and ZIP Code</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">San Rafael, CA, 94901</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100738</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Gender</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Male</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100738</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Date of Birth</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/4/1977</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100738</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Name</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Willson Mary</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100456</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>City, State and ZIP Code</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">San Rafael, CA, 94113</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100456</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Gender</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Female</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100456</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Date of Birth</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2/1/1972</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>91100456</TD></TR></TBODY></TABLE>



I need to convert it into a table like this (one line per employee) in Sheet2:


<TABLE style="WIDTH: 366pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=487 border=0><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=88 height=20>Employee ID</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=94>Name</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 117pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=156>City, State and ZIP Code</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Gender</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=85>Date of Birth</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>91100738</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Willson David</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">San Rafael, CA, 94901</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Male</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3/4/1977</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>91100456</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Willson Mary</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">San Rafael, CA, 94113</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Female</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2/1/1972</TD></TR></TBODY></TABLE>


Please suggest a VBA solution.

Thank you!
<!-- / message -->

 

[hiker95]

Learn_Excel,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C
1	Question	response	Employee ID
2	Name	Willson David	91100738
3	City, State and ZIP Code	San Rafael, CA, 94901	91100738
4	Gender	Male	91100738
5	Date of Birth	3/4/1977	91100738
6	Name	Willson Mary	91100456
7	City, State and ZIP Code	San Rafael, CA, 94113	91100456
8	Gender	Female	91100456
9	Date of Birth	2/1/1972	91100456
10
Sheet1

After the macro in worksheet Sheet2:

Excel Workbook
	A	B	C	D	E
1	Employee ID	Name	City, State and ZIP Code	Gender	Date of Birth
2	91100738	Willson David	San Rafael, CA, 94901	Male	3/4/1977
3	91100456	Willson Mary	San Rafael, CA, 94113	Female	2/1/1972
4
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 03/24/2011
' http://www.mrexcel.com/forum/showthread.php?t=538587
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Sheet2!A1)") Then Worksheets.Add(After:=w1).Name = "Sheet2"
Set w2 = Worksheets("Sheet2")
With w2.Range("A1:E1")
  .Value = [{"Employee ID","Name","City, State and ZIP Code","Gender","Date of Birth"}]
  .Font.Bold = True
End With
LR = w1.Cells(Rows.Count, 3).End(xlUp).Row
For a = 2 To LR Step 4
  NR = w2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  w2.Range("A" & NR).Value = w1.Range("C" & a).Value
  w2.Range("B" & NR).Resize(, 4).Value = Application.Transpose(w1.Range("B" & a).Resize(4).Value)
Next a
w2.Range("E2:E" & NR).NumberFormat = "m/d/yyyy"
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro:


If your raw data does not have all the 4 titles for each group, then let me know, and I will re-write the macro.

 

[Learn_Excel]

Actually, the titles exist in my real table but there are 15 more questions. It's a survey. Do I have to enter them all? Is there a way to transpose without entering the table headers?

 

[hiker95]

Learn_Excel,


Would you like me to adjust the macro code so that the macro will not add the titles to worksheet Sheet2 in row 1?

 

[hiker95]

Learn_Excel,

No titles added to worksheet Sheet2.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataV2()
' hiker95, 03/24/2011
' http://www.mrexcel.com/forum/showthread.php?t=538587
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
LR = w1.Cells(Rows.Count, 3).End(xlUp).Row
For a = 2 To LR Step 4
  NR = w2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  w2.Range("A" & NR).Value = w1.Range("C" & a).Value
  w2.Range("B" & NR).Resize(, 4).Value = Application.Transpose(w1.Range("B" & a).Resize(4).Value)
Next a
w2.Range("E2:E" & NR).NumberFormat = "m/d/yyyy"
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.

 

[Learn_Excel]

Hiker95, Thank you for your reply. I am sorry for the late response. I was out of town. Your code works if the questionnaire consists of 4 questions. What if I don't know how many questions it has?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Learn_Excel]

In addition, the questions are not in the same order. It's a mess. So, basically, if someone skipped a question, there is no blank answer, the line is just missing.

 

[hiker95]

Learn_Excel,

To make this work correctly, we need to see your raw data on one worksheet, and your results manually formatted on another worksheet.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net