Comparing data columns

[Gavim Francis]

Hi, I've just about reached the limit of my knowledge on this query
and I'm hoping someone can help me on it.
In column A I have a list of individual client sales e.g -

client aardvark
client aardvark
client bear
client cat
client cat
client cat
client camel
client camel

Column B lists the name of the company that sold the product to the
clients in column A. Column B will only list 1 of 2 companies. Both
companies could have sold to the same client but not always.

Column C shows the sale amount.

I've created a Pivot table which lists the sum total prices of the
sales to the clients in Column A by the companies in Column B. This
is fine but I'd like to compare the total prices when Company X and
Company Y have sold to Client Bear for example. This can be shown in
the Pivot table but it also lists when only Company X (not company Y)
has sold to client Camel.

So basically I'd like some way to produce a total of all sales by
Company X when they have sold to the same client as Company Y. I'm
thinking that Vlookup is needed but I'm really not sure where to go.

Cheers for any help.

 

[JulieD]

Hi Gavim

one way to do this is to add another column into your existing data range
then you can use this column to filter the pivot table - i've put my data
range (of your example data) in A2:C9 and added another column called
Include in D

in column D i've used the following formula to put a "yes" in those columns
where duplicates of the client name appear in column A. This column can
then be added to the column area of the pivot table and then filtered so
that only "yes" items are displayed.

the formula in D2 is
=IF(COUNTIF($A$2:$A$9,A2)>1,"yes","")
fill the formula down

Hope this gives you what you need.

Cheers
JulieD