Columns to Rows

[leveyc]

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-comfficeffice" /><o></o>

 

[rallcorn]

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]

Thanks, I think I made the explanation to easy, is there a formula as there is more I need to do

 

[rallcorn]

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