Conditional Convert rows to columns

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:
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

just1joe

Board Regular
Joined
Apr 15, 2003
Messages
79
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,374
Members
410,911
Latest member
AniEx
Top