Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > transforming poll data from rows to colums
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default transforming poll data from rows to colums
Hi,
I need to rearrange data on two sheets to a third. As a amateur I would
appreciate any help!
Thank you
Ulf


Sheet01 contains "data" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 1 Member? 1 Yes
6402 3 Prefers? 3 Milk
6403 1 Member? 2 No
6403 3 Prefers? 1 Beer
6403 4 Age? 55

Sheet02 contains "text" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 2 Name? steve
6402 5 City? London
6403 2 Name? john
6403 5 City? Paris

What I need is Sheet03 with one row per ReplyId - and one column per QuestNo

ReplyId Member? Name? Prefers? Age? City?
6402 1 Yes Steve 3 Milk London
6403 2 No John 1 Beer 55 Paris


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default transforming poll data from rows to colums
I assumed there were header rows on all 3 sheets as shown in your data before
the program is run. The 3 sheet names are sheet1, sheet2,sheet3. Program
uses header row on sheet 3 for looking up the questions. Adding more columns
to sheet3 will not require any changes to the program.

Sub combinedata()

Call CombineSheet("sheet1")
Call CombineSheet("sheet2")

End Sub
Sub CombineSheet(ByVal SheetName As String)

'skip header row

With Sheets("Sheet3")
Sh3RowCount = .Cells(Rows.Count, "A"). _
End(xlUp).Row
LastCol = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set QuestRange = .Range( _
.Cells(1, "B"), .Cells(1, LastCol))
End With

With Sheets(SheetName)
RowCount = 2
Do While .Cells(RowCount, "A") < ""
'find ReplyId
ReplyID = .Cells(RowCount, "A").Value
Question = .Cells(RowCount, "C").Value
Answer = Trim(.Cells(RowCount, "D").Value)
Answer = Answer & " " & _
Trim(.Cells(RowCount, "E").Value)
With Sheets("Sheet3")
Set Sh3IDRange = .Range( _
.Cells(2, "A"), _
.Cells(Sh3RowCount, "A"))
Set c1 = Sh3IDRange.Find( _
what:=ReplyID, _
LookIn:=xlValues)
If Not c1 Is Nothing Then
InsertRow = c1.Row
Else
Sh3RowCount = Sh3RowCount + 1
InsertRow = Sh3RowCount
.Cells(InsertRow, "A") = _
ReplyID
End If

Set c2 = QuestRange.Find( _
what:=Question, _
LookIn:=xlValues)
If Not c2 Is Nothing Then
.Cells(InsertRow, c2.Column).Value = _
Answer
Else
MsgBox ("Could Not find question : " & _
Question)
End If

End With
RowCount = RowCount + 1
Loop

End With
End Sub


"ulfb" wrote:

Hi,
I need to rearrange data on two sheets to a third. As a amateur I would
appreciate any help!
Thank you
Ulf


Sheet01 contains "data" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 1 Member? 1 Yes
6402 3 Prefers? 3 Milk
6403 1 Member? 2 No
6403 3 Prefers? 1 Beer
6403 4 Age? 55

Sheet02 contains "text" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 2 Name? steve
6402 5 City? London
6403 2 Name? john
6403 5 City? Paris

What I need is Sheet03 with one row per ReplyId - and one column per QuestNo

ReplyId Member? Name? Prefers? Age? City?
6402 1 Yes Steve 3 Milk London
6403 2 No John 1 Beer 55 Paris


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default transforming poll data from rows to colums
Thank you Joel

That helps a lot! However, sheet3 has no headers since questions vary, both
i number and contents. Headers need to be created from unique entries in
QuestName, sheet1 and sheet2
Any suggestions for that?
Ulf


"Joel" wrote:

I assumed there were header rows on all 3 sheets as shown in your data before
the program is run. The 3 sheet names are sheet1, sheet2,sheet3. Program
uses header row on sheet 3 for looking up the questions. Adding more columns
to sheet3 will not require any changes to the program.

Sub combinedata()

Call CombineSheet("sheet1")
Call CombineSheet("sheet2")

End Sub
Sub CombineSheet(ByVal SheetName As String)

'skip header row

With Sheets("Sheet3")
Sh3RowCount = .Cells(Rows.Count, "A"). _
End(xlUp).Row
LastCol = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set QuestRange = .Range( _
.Cells(1, "B"), .Cells(1, LastCol))
End With

With Sheets(SheetName)
RowCount = 2
Do While .Cells(RowCount, "A") < ""
'find ReplyId
ReplyID = .Cells(RowCount, "A").Value
Question = .Cells(RowCount, "C").Value
Answer = Trim(.Cells(RowCount, "D").Value)
Answer = Answer & " " & _
Trim(.Cells(RowCount, "E").Value)
With Sheets("Sheet3")
Set Sh3IDRange = .Range( _
.Cells(2, "A"), _
.Cells(Sh3RowCount, "A"))
Set c1 = Sh3IDRange.Find( _
what:=ReplyID, _
LookIn:=xlValues)
If Not c1 Is Nothing Then
InsertRow = c1.Row
Else
Sh3RowCount = Sh3RowCount + 1
InsertRow = Sh3RowCount
.Cells(InsertRow, "A") = _
ReplyID
End If

Set c2 = QuestRange.Find( _
what:=Question, _
LookIn:=xlValues)
If Not c2 Is Nothing Then
.Cells(InsertRow, c2.Column).Value = _
Answer
Else
MsgBox ("Could Not find question : " & _
Question)
End If

End With
RowCount = RowCount + 1
Loop

End With
End Sub


"ulfb" wrote:

Hi,
I need to rearrange data on two sheets to a third. As a amateur I would
appreciate any help!
Thank you
Ulf


Sheet01 contains "data" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 1 Member? 1 Yes
6402 3 Prefers? 3 Milk
6403 1 Member? 2 No
6403 3 Prefers? 1 Beer
6403 4 Age? 55

Sheet02 contains "text" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 2 Name? steve
6402 5 City? London
6403 2 Name? john
6403 5 City? Paris

What I need is Sheet03 with one row per ReplyId - and one column per QuestNo

ReplyId Member? Name? Prefers? Age? City?
6402 1 Yes Steve 3 Milk London
6403 2 No John 1 Beer 55 Paris


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default transforming poll data from rows to colums
I made a small change to create the header row on sheet 3

Sub combinedata()

With Sheets("Sheet3")
.Cells(1, "A") = "ReplyId"
End With
Call CombineSheet("sheet1")
Call CombineSheet("sheet2")

End Sub
Sub CombineSheet(ByVal SheetName As String)

'skip header row


With Sheets("Sheet3")
Sh3RowCount = .Cells(Rows.Count, "A"). _
End(xlUp).Row
LastCol = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set QuestRange = .Range( _
.Cells(1, "A"), .Cells(1, LastCol))
End With

With Sheets(SheetName)
RowCount = 2
Do While .Cells(RowCount, "A") < ""
'find ReplyId
ReplyID = .Cells(RowCount, "A").Value
Question = .Cells(RowCount, "C").Value
Answer = Trim(.Cells(RowCount, "D").Value)
Answer = Answer & " " & _
Trim(.Cells(RowCount, "E").Value)
With Sheets("Sheet3")
Set Sh3IDRange = .Range( _
.Cells(2, "A"), _
.Cells(Sh3RowCount, "A"))
Set c1 = Sh3IDRange.Find( _
what:=ReplyID, _
LookIn:=xlValues)
If Not c1 Is Nothing Then
InsertRow = c1.Row
Else
Sh3RowCount = Sh3RowCount + 1
InsertRow = Sh3RowCount
.Cells(InsertRow, "A") = _
ReplyID
End If

Set c2 = QuestRange.Find( _
what:=Question, _
LookIn:=xlValues)
If Not c2 Is Nothing Then
.Cells(InsertRow, c2.Column).Value = _
Answer
Else
LastCol = LastCol + 1
.Cells(1, LastCol).Value = _
Question
.Cells(InsertRow, LastCol).Value = _
Answer
Set QuestRange = .Range( _
.Cells(1, "A"), .Cells(1, LastCol))
End If

End With
RowCount = RowCount + 1
Loop

End With
End Sub


"ulfb" wrote:

Thank you Joel

That helps a lot! However, sheet3 has no headers since questions vary, both
i number and contents. Headers need to be created from unique entries in
QuestName, sheet1 and sheet2
Any suggestions for that?
Ulf


"Joel" wrote:

I assumed there were header rows on all 3 sheets as shown in your data before
the program is run. The 3 sheet names are sheet1, sheet2,sheet3. Program
uses header row on sheet 3 for looking up the questions. Adding more columns
to sheet3 will not require any changes to the program.

Sub combinedata()

Call CombineSheet("sheet1")
Call CombineSheet("sheet2")

End Sub
Sub CombineSheet(ByVal SheetName As String)

'skip header row

With Sheets("Sheet3")
Sh3RowCount = .Cells(Rows.Count, "A"). _
End(xlUp).Row
LastCol = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set QuestRange = .Range( _
.Cells(1, "B"), .Cells(1, LastCol))
End With

With Sheets(SheetName)
RowCount = 2
Do While .Cells(RowCount, "A") < ""
'find ReplyId
ReplyID = .Cells(RowCount, "A").Value
Question = .Cells(RowCount, "C").Value
Answer = Trim(.Cells(RowCount, "D").Value)
Answer = Answer & " " & _
Trim(.Cells(RowCount, "E").Value)
With Sheets("Sheet3")
Set Sh3IDRange = .Range( _
.Cells(2, "A"), _
.Cells(Sh3RowCount, "A"))
Set c1 = Sh3IDRange.Find( _
what:=ReplyID, _
LookIn:=xlValues)
If Not c1 Is Nothing Then
InsertRow = c1.Row
Else
Sh3RowCount = Sh3RowCount + 1
InsertRow = Sh3RowCount
.Cells(InsertRow, "A") = _
ReplyID
End If

Set c2 = QuestRange.Find( _
what:=Question, _
LookIn:=xlValues)
If Not c2 Is Nothing Then
.Cells(InsertRow, c2.Column).Value = _
Answer
Else
MsgBox ("Could Not find question : " & _
Question)
End If

End With
RowCount = RowCount + 1
Loop

End With
End Sub


"ulfb" wrote:

Hi,
I need to rearrange data on two sheets to a third. As a amateur I would
appreciate any help!
Thank you
Ulf


Sheet01 contains "data" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 1 Member? 1 Yes
6402 3 Prefers? 3 Milk
6403 1 Member? 2 No
6403 3 Prefers? 1 Beer
6403 4 Age? 55

Sheet02 contains "text" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 2 Name? steve
6402 5 City? London
6403 2 Name? john
6403 5 City? Paris

What I need is Sheet03 with one row per ReplyId - and one column per QuestNo

ReplyId Member? Name? Prefers? Age? City?
6402 1 Yes Steve 3 Milk London
6403 2 No John 1 Beer 55 Paris


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 20
Default transforming poll data from rows to colums
Thank you, I am very grateful!
Ulf



"Joel" wrote:

I made a small change to create the header row on sheet 3

Sub combinedata()

With Sheets("Sheet3")
.Cells(1, "A") = "ReplyId"
End With
Call CombineSheet("sheet1")
Call CombineSheet("sheet2")

End Sub
Sub CombineSheet(ByVal SheetName As String)

'skip header row


With Sheets("Sheet3")
Sh3RowCount = .Cells(Rows.Count, "A"). _
End(xlUp).Row
LastCol = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set QuestRange = .Range( _
.Cells(1, "A"), .Cells(1, LastCol))
End With

With Sheets(SheetName)
RowCount = 2
Do While .Cells(RowCount, "A") < ""
'find ReplyId
ReplyID = .Cells(RowCount, "A").Value
Question = .Cells(RowCount, "C").Value
Answer = Trim(.Cells(RowCount, "D").Value)
Answer = Answer & " " & _
Trim(.Cells(RowCount, "E").Value)
With Sheets("Sheet3")
Set Sh3IDRange = .Range( _
.Cells(2, "A"), _
.Cells(Sh3RowCount, "A"))
Set c1 = Sh3IDRange.Find( _
what:=ReplyID, _
LookIn:=xlValues)
If Not c1 Is Nothing Then
InsertRow = c1.Row
Else
Sh3RowCount = Sh3RowCount + 1
InsertRow = Sh3RowCount
.Cells(InsertRow, "A") = _
ReplyID
End If

Set c2 = QuestRange.Find( _
what:=Question, _
LookIn:=xlValues)
If Not c2 Is Nothing Then
.Cells(InsertRow, c2.Column).Value = _
Answer
Else
LastCol = LastCol + 1
.Cells(1, LastCol).Value = _
Question
.Cells(InsertRow, LastCol).Value = _
Answer
Set QuestRange = .Range( _
.Cells(1, "A"), .Cells(1, LastCol))
End If

End With
RowCount = RowCount + 1
Loop

End With
End Sub


"ulfb" wrote:

Thank you Joel

That helps a lot! However, sheet3 has no headers since questions vary, both
i number and contents. Headers need to be created from unique entries in
QuestName, sheet1 and sheet2
Any suggestions for that?
Ulf


"Joel" wrote:

I assumed there were header rows on all 3 sheets as shown in your data before
the program is run. The 3 sheet names are sheet1, sheet2,sheet3. Program
uses header row on sheet 3 for looking up the questions. Adding more columns
to sheet3 will not require any changes to the program.

Sub combinedata()

Call CombineSheet("sheet1")
Call CombineSheet("sheet2")

End Sub
Sub CombineSheet(ByVal SheetName As String)

'skip header row

With Sheets("Sheet3")
Sh3RowCount = .Cells(Rows.Count, "A"). _
End(xlUp).Row
LastCol = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set QuestRange = .Range( _
.Cells(1, "B"), .Cells(1, LastCol))
End With

With Sheets(SheetName)
RowCount = 2
Do While .Cells(RowCount, "A") < ""
'find ReplyId
ReplyID = .Cells(RowCount, "A").Value
Question = .Cells(RowCount, "C").Value
Answer = Trim(.Cells(RowCount, "D").Value)
Answer = Answer & " " & _
Trim(.Cells(RowCount, "E").Value)
With Sheets("Sheet3")
Set Sh3IDRange = .Range( _
.Cells(2, "A"), _
.Cells(Sh3RowCount, "A"))
Set c1 = Sh3IDRange.Find( _
what:=ReplyID, _
LookIn:=xlValues)
If Not c1 Is Nothing Then
InsertRow = c1.Row
Else
Sh3RowCount = Sh3RowCount + 1
InsertRow = Sh3RowCount
.Cells(InsertRow, "A") = _
ReplyID
End If

Set c2 = QuestRange.Find( _
what:=Question, _
LookIn:=xlValues)
If Not c2 Is Nothing Then
.Cells(InsertRow, c2.Column).Value = _
Answer
Else
MsgBox ("Could Not find question : " & _
Question)
End If

End With
RowCount = RowCount + 1
Loop

End With
End Sub


"ulfb" wrote:

Hi,
I need to rearrange data on two sheets to a third. As a amateur I would
appreciate any help!
Thank you
Ulf


Sheet01 contains "data" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 1 Member? 1 Yes
6402 3 Prefers? 3 Milk
6403 1 Member? 2 No
6403 3 Prefers? 1 Beer
6403 4 Age? 55

Sheet02 contains "text" replies:
ReplyId QuestNo QuestName QuestText QuestAltValue
6402 2 Name? steve
6402 5 City? London
6403 2 Name? john
6403 5 City? Paris

What I need is Sheet03 with one row per ReplyId - and one column per QuestNo

ReplyId Member? Name? Prefers? Age? City?
6402 1 Yes Steve 3 Milk London
6403 2 No John 1 Beer 55 Paris


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Chart disappears if you hide rows/colums with data. Philip Gump Excel Discussion (Misc queries) 1 April 3rd 06 04:36 AM
converting data in colums to rows Evad Excel Discussion (Misc queries) 8 February 1st 06 05:49 AM
Can I rearrange data entered in rows into colums in Excel? Robin Excel Discussion (Misc queries) 1 January 30th 06 07:48 PM
analyzing poll data ulfb Excel Programming 1 December 10th 04 09:27 PM
Macro & Command Key to change data from Colums to rows Danno[_5_] Excel Programming 3 June 10th 04 02:53 PM


All times are GMT +1. The time now is 04:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement