Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing columns of data | Excel Discussion (Misc queries) | |||
Comparing two columns of data | Excel Discussion (Misc queries) | |||
Comparing 2 columns of data | New Users to Excel | |||
Comparing Data in 2 columns | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |