Conditional Convert rows to columns

J

just1joe

Board Regular
Joined
Apr 15, 2003
Messages
79
I am trying to convert files from a format which shows all of the items in mulitple rows to one that shows each item in one row. For each srv_name, each user_id can have up to three responses in srva_text. These responses have srvq_seq_num
1, 2, 3 and should go to the columns E, F and G of a new worksheet, but there is a chance there will not be data for each srvq_seq_num.
Book1 (version 1).xls
BCDEFGHIJ
1srv_namesrvq_textsrva_textsrvq_seq_numsrva_seq_numsrv_typeusr_idusr_fnameusr_lname
2Thursday11:00BreakoutEvaluationSession1011058546795155822137MikeYamamoto
3Thursday11:00BreakoutEvaluationRating823058546795155822137MikeYamamoto
4Thursday11:00BreakoutEvaluationWhy?itwascool3058546795155822137MikeYamamoto
5Thursday9:30BreakoutEvaluationSession714058546795155822137MikeYamamoto
6Thursday9:30BreakoutEvaluationRating625058546795155822137MikeYamamoto
7Thursday9:30BreakoutEvaluationWhy?asdf3058546795155822137MikeYamamoto
8ThursdayKeynoteEvaluationSession1011058546795155822137MikeYamamoto
9ThursdayKeynoteEvaluationRating625058546795155822137MikeYamamoto
10ThursdayKeynoteEvaluationWhy?idunno3058546795155822137MikeYamamoto
11SaturdayEncoreEvaluationSession1011058546795155822139RaymondPlante
12SaturdayEncoreEvaluationRating823058546795155822139RaymondPlante
13SaturdayEncoreEvaluationWhy?Fun31058546795155822139RaymondPlante
14Thursday9:30BreakoutEvaluationSession1011058546795155822139RaymondPlante
15Thursday9:30BreakoutEvaluationRating823058546795155822139RaymondPlante
16Thursday9:30BreakoutEvaluationWhy?fun3058546795155822139RaymondPlante
17ThursdayKeynoteEvaluationSession912058546795155822139RaymondPlante
18ThursdayKeynoteEvaluationRating823058546795155822139RaymondPlante
19ThursdayKeynoteEvaluationWhy?h3058546795155822139RaymondPlante
20Friday11:00BreakoutEvaluationSession615058546795155822140JoeLantis
21Friday11:00BreakoutEvaluationWhy?hnmsd3058546795155822140JoeLantis
22Friday9:30BreakoutEvaluationWhy?grhh3058546795155822140JoeLantis
23SaturdayEncoreEvaluationSession813058546795155822140JoeLantis
24SaturdayEncoreEvaluationRating922058546795155822140JoeLantis
25SaturdayEncoreEvaluationWhy?Sad34058546795155822140JoeLantis
Sheet1


to this:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=8><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1 (version 1).xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=8><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=8><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value="" selected>G10</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">srv_name</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">usr_id</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">usr_fname</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">usr_lname</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Session</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP:
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
The second worksheet would look like this:
Book1 (version 1).xls
ABCDEFG
1srv_nameusr_idusr_fnameusr_lnameSessionRatingWhy?
2Thursday11:00BreakoutEvaluation58546795155822137MikeYamamoto108itwascool
3Thursday9:30BreakoutEvaluation58546795155822137MikeYamamoto76asdf
4ThursdayKeynoteEvaluation58546795155822137MikeYamamoto106idunno
5SaturdayEncoreEvaluation58546795155822139RaymondPlante108Fun
6Thursday9:30BreakoutEvaluation58546795155822139RaymondPlante108fun
7ThursdayKeynoteEvaluation58546795155822139RaymondPlante98h
8Friday11:00BreakoutEvaluation58546795155822140JoeLantis6hnmsd
9Friday9:30BreakoutEvaluation58546795155822140JoeLantisgrhh
10SaturdayEncoreEvaluation58546795155822140JoeLantis8
Sheet4
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
825
gvsumich
G
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
315
VBAEXCELNew
V
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
348
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,904
Messages
6,122,169
Members
449,070
Latest member
webster33

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top