Pivot Question Urgent!!

[student]

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]

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.)

 

[Guest]

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.

 

[Guest]

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")

 

[Ken Wright]

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.

 

[student]

Thanks every body. I made 2 seperate pivot tables.