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>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,508
Messages
5,511,709
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top