Pivot Tabling Information

D

dm64

New Member
Joined
Jul 9, 2009
Messages
23
<table width="526" border="0" cellpadding="0" cellspacing="0"><col style="width: 75pt;" width="100"> <col style="width: 83pt;" width="111"> <col style="width: 98pt;" width="131"> <col style="width: 138pt;" width="184"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 75pt;" width="100" height="17">Vendor1</td> <td class="xl24" style="border-left: medium none; width: 83pt;" width="111">Vendor2</td> <td class="xl24" style="border-left: medium none; width: 98pt;" width="131">Vendor3</td> <td class="xl24" style="border-left: medium none; width: 138pt;" width="184">Project Leader</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">ABC Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">TPN Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">HYE Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Fred</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">IOP Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Harry</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">TPN Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">ABC Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Mary</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">HYE Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Harry</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">SSW Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;">John</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">ABC Co</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Steve</td> </tr> </tbody></table>

Hi

Above is an extract from table of information Im working with in a Project Tracker.

Some projects have more than one vendor involved and the source data is setup to capture on each row....each row in the sheet is a particular project.

Im trying to get my head around how to pivot the Vendor information all together. I dont care if the vendor name is listed under Vendor1,2 or 3....all I want to get to is to show ABC Co is engaged on 3 projects, HYE Co is on 2 projects etc etc.

Any assistance will be greatly appreciated!

Thanks
Dennis
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
you could use a countif, like:
=COUNTIF(A:C,"*"&"ABC"&"*")

etc for the others, or is there more to the problem than a count?
 
Upvote 0
Did you try getting the pivot work this for you. I just tried this way looks okay for me. But not sure if you want the name of the project to show in text.

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 3328" width=78><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 5248" span=2 width=123><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; FONT-FAMILY: Calibri; BACKGROUND: #c2d69a; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67 height=16 width=78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; FONT-FAMILY: Calibri; BACKGROUND: #c2d69a; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67 width=123>
Vendor3 Count​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; FONT-FAMILY: Calibri; BACKGROUND: #c2d69a; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67 width=123>
Vendor2 Count​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=16>
ABC Co​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl65>
1​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64>
1​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=16>
Fred​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>
1​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
1​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=16>
Steve​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=16>
HYE Co​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64>
​
</TD></TR>


​
<TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2629299 class=xl66 height=16>
Harry​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=16>
IOP Co​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64>
​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=16>
Harry​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=16>
SSW Co​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64>
​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=16>
John​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64 height=16>
TPN Co​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #d7e4bc; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl64>
1​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=16>
Mary​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>
​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>
1​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #948b54; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68 height=16>
Grand Total​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #948b54; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl69>
1​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #948b54; COLOR: black; FONT-SIZE: 9pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl68>
2​
</TD></TR></TBODY></TABLE>
 
Upvote 0
Thanks for the replies.

Pedie - its probably really simple but how did you set up the pivot to get that result?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,224,613
Messages
6,179,894
Members
452,948
Latest member
Dupuhini

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