Pivot Question Urgent!!

hi

I have two columns

Ins.Contractor Int.Contractor
A B
A C
B C
C A
A A
D X

I want display the count of both the contractors grouped by Contractor name

Contractor Count of Ins. count of Int.
A 3 2
B 1 1
C 1 2
D 1 0
X 0 1

Is it possible with Pivot.

Dave
Wed Sep 22 15:33:58 CDT 2004

If you rearrange your data (or a copy of your data, it would make it simpler.

I moved the stuff in column B (not including the header) under the last entry in
column A.

I put 1 in column B for all the INS Contr.
I put 1 in column C for all the INT contr.
I labeled Column A "Contr"

Then I could create the pivottable easily. (I used sum instead of count, but
since I was summing 1's, it was the same.)



student wrote:
>
> hi
>
> I have two columns
>
> Ins.Contractor Int.Contractor
> A B
> A C
> B C
> C A
> A A
> D X
>
> I want display the count of both the contractors grouped by Contractor name
>
> Contractor Count of Ins. count of Int.
> A 3 2
> B 1 1
> C 1 2
> D 1 0
> X 0 1
>
> Is it possible with Pivot.

--

Dave Peterson
ec35720@msn.com

MK
Wed Sep 22 15:47:03 CDT 2004

I would do three queries:

Q1 to summarize INS
Q2 to summarize INT
Q3 to link the results of Q1 and Q2.

Can't think of a way to use pivot tables.


"student" wrote:

> hi
>
> I have two columns
>
> Ins.Contractor Int.Contractor
> A B
> A C
> B C
> C A
> A A
> D X
>
> I want display the count of both the contractors grouped by Contractor name
>
> Contractor Count of Ins. count of Int.
> A 3 2
> B 1 1
> C 1 2
> D 1 0
> X 0 1
>
> Is it possible with Pivot.
>
>
>

Ken
Wed Sep 22 16:52:05 CDT 2004

Change your layout so it looks like this, ie add just an extra column, copy the
Contractor type (Ins.Cont..) down the left, then move the second column of data
under the first and copy the type down against the left for those entries
also.:-

A B
Type Contract
Ins.Contractor A
Ins.Contractor A
Ins.Contractor B
Ins.Contractor C
Ins.Contractor A
Ins.Contractor D
Int.Contractor B
Int.Contractor C
Int.Contractor C
Int.Contractor A
Int.Contractor A
Int.Contractor X


Now Pivot it, put Contract into the Row fields, Type into Column fields and then
ALSO drop Contract into the Data field.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"student" <agarwalp@lucent.com> wrote in message
news:%23VYtfHIoEHA.3668@TK2MSFTNGP15.phx.gbl...
> hi
>
> I have two columns
>
> Ins.Contractor Int.Contractor
> A B
> A C
> B C
> C A
> A A
> D X
>
> I want display the count of both the contractors grouped by Contractor name
>
> Contractor Count of Ins. count of Int.
> A 3 2
> B 1 1
> C 1 2
> D 1 0
> X 0 1
>
> Is it possible with Pivot.
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.766 / Virus Database: 513 - Release Date: 17/09/2004

student
Thu Sep 23 02:32:43 CDT 2004

Thanks every body. I made 2 seperate pivot tables.
"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OUkRq5OoEHA.896@TK2MSFTNGP12.phx.gbl...
> Change your layout so it looks like this, ie add just an extra column,
copy the
> Contractor type (Ins.Cont..) down the left, then move the second column of
data
> under the first and copy the type down against the left for those entries
> also.:-
>
> A B
> Type Contract
> Ins.Contractor A
> Ins.Contractor A
> Ins.Contractor B
> Ins.Contractor C
> Ins.Contractor A
> Ins.Contractor D
> Int.Contractor B
> Int.Contractor C
> Int.Contractor C
> Int.Contractor A
> Int.Contractor A
> Int.Contractor X
>
>
> Now Pivot it, put Contract into the Row fields, Type into Column fields
and then
> ALSO drop Contract into the Data field.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "student" <agarwalp@lucent.com> wrote in message
> news:%23VYtfHIoEHA.3668@TK2MSFTNGP15.phx.gbl...
> > hi
> >
> > I have two columns
> >
> > Ins.Contractor Int.Contractor
> > A B
> > A C
> > B C
> > C A
> > A A
> > D X
> >
> > I want display the count of both the contractors grouped by Contractor
name
> >
> > Contractor Count of Ins. count of Int.
> > A 3 2
> > B 1 1
> > C 1 2
> > D 1 0
> > X 0 1
> >
> > Is it possible with Pivot.
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.766 / Virus Database: 513 - Release Date: 17/09/2004
>
>

MK
Wed Sep 22 15:59:03 CDT 2004

Another option:

Contractor INS INT

A countif(ins range,"A") countif(int
range,"A")
B countif(ins range,"B") etc..
C countif(ins range,"C")





"student" wrote:

> hi
>
> I have two columns
>
> Ins.Contractor Int.Contractor
> A B
> A C
> B C
> C A
> A A
> D X
>
> I want display the count of both the contractors grouped by Contractor name
>
> Contractor Count of Ins. count of Int.
> A 3 2
> B 1 1
> C 1 2
> D 1 0
> X 0 1
>
> Is it possible with Pivot.
>
>
>