Columns to Rows

leveyc

Board Regular
Joined
Oct 14, 2009
Messages
155
Hi, I’m sure this is easy, but for the life of me I cannot seem to get this to work. I have a list of 150 site addresses, each site is visited four times throughout the year, at each visit a ticket number is generated with one of the four reference codes (Each site should have one of each of the four codes by year’s end). When I pull a report the sites are listed up to four times in column A, ticket number in column B and each of the four reference codes in column C. What I would like to do is to have a site list of the 150 addresses, then lookup the raw data and list in the same row, columns B, C, D and E each of the ref codes so I can see what still needs to be completed the year. Row 1 A1 - Site 1 B1 – Ref Code C1 – Ref Code D1 – Ref Code….All help greatly appreciated<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

rallcorn

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
If data already set up with a separate row for each site for each visit, I'd use a pivot table:
Site in Row Labels
Ref code in both the Column Labels & Values(as sum should work for this)

Data Sheet:
<TABLE style="WIDTH: 126pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=168 border=0><COLGROUP><COL style="WIDTH: 42pt" span=3 width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 42pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=56 height=17>Site</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 42pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=56>Ticket #</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 42pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=56>Ref Code</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site 1</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>800</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site10</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>801</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site 1</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>802</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site10</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>803</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site10</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>804</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site10</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>805</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>4</TD></TR></TBODY></TABLE>

Pivot Table let's you easily see that Site 1 doesn't yet have Ref Codes 3 & 4

<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=245 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 4181" width=98><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 4480" width=105><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 597" span=3 width=14><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; WIDTH: 74pt; COLOR: black; BORDER-BOTTOM: #ffffff; FONT-FAMILY: 'Times New Roman'; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=98 height=17>Sum of Ref Code</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; WIDTH: 79pt; COLOR: black; BORDER-BOTTOM: #ffffff; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=105>Column Labels</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; WIDTH: 11pt; COLOR: black; BORDER-BOTTOM: #ffffff; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=14></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; WIDTH: 11pt; COLOR: black; BORDER-BOTTOM: #ffffff; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=14></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; WIDTH: 11pt; COLOR: black; BORDER-BOTTOM: #ffffff; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" width=14></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: 'Times New Roman'; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=17>Row Labels</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>1</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>2</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>3</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ffffff; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: 'Times New Roman'; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site 1</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Site10</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>3</TD><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" align=right>4</TD></TR></TBODY></TABLE>
 

leveyc

Board Regular
Joined
Oct 14, 2009
Messages
155
Thanks, I think I made the explanation to easy, is there a formula as there is more I need to do
 

rallcorn

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
no formula because it is in a pivot table. what else do you need to accomplish? May be able to do in pivot table also
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,816
Members
418,414
Latest member
ECMdusty

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
Top