Comparing data columns
 

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.

Comparing data columns
 

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

"Gavim Francis" wrote in message
m...
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.

Comparing data columns
 

Hi JulieD, many thanks. Unfortunately due to the way that the data was
sent to me, there are sometimes many entries in Column A even though 1
company in Column B made the sale. This is because the figures in
Column A are indivual sales to the client and not a total.
With the formula that was sent, Column D will show Yes if Company X has
sold to Client Aardark twice (because Client Aadvark will appear twice
in Column A).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Comparing data columns
 

You could add a 4th column that indicates the number of companies from which
the client has made purchases. Let's say you use column D for that.

Assuming headers in row 1, data in rows 2:100, in D2 put this formula and copy
down through D100:


=IF(SUMPRODUCT(--($A$2:$A$100=A2),--($B$2:$B$100=B2))=COUNTIF($A$2:$A$100,$A2),1,2)

Then add that field to the Pivot Table (maybe as a page field?) and display
only the rows where the value is 2.


On 26 Jan 2005 07:33:09 -0800, (Gavim Francis) wrote:

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.