Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting 2 lists
I'm sure there's a name for what I'm trying to do, but I don't know it so I
can't search previous questions for answers. Please help! I have 2 lists: List 1, column A - Customer names List 1, column B - annual sales 2003 List 2, column C - Customer names (some of which are also on List 1) List 2, column D - annual sales 2004 I want to match up customers that exist in both lists and be able to compare 2003 sales in a column right next to 2004 sales. The customers that don't match up can be listed below. |
#2
|
|||
|
|||
Hi, Am I correct that the two lists are on the same sheet? In that case, the following formula in E2 should work (assuming that trow 1 contain headers and the data start at row 2). Change the range indices to suit your data. =IF(SUMPRODUCT(--(C2=$A$2:$A$100))<0,SUMPRODUCT(--(C2=$A$2:$A$100),$B$2:$B$100),"") This formula would leave the cells blank for 2004 names that do not have a match in the 2003 info.. A simpler formula would also work, but would show zeros when there is no match (caveat: if there is a match and if it so happens that the 2003 sales was 0 for that customer, then you wouldn't be able to distinguish that 0 from the 'no match' zeros). The first formula avoids that situation. =SUMPRODUCT(--(C2=$A$2:$A$100),($B$2:$B$100)) Regards, B.R. Ramachandran "jtoy" wrote: I'm sure there's a name for what I'm trying to do, but I don't know it so I can't search previous questions for answers. Please help! I have 2 lists: List 1, column A - Customer names List 1, column B - annual sales 2003 List 2, column C - Customer names (some of which are also on List 1) List 2, column D - annual sales 2004 I want to match up customers that exist in both lists and be able to compare 2003 sales in a column right next to 2004 sales. The customers that don't match up can be listed below. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel :sorting lists? | Excel Discussion (Misc queries) | |||
Sorting lists to remove double entries | Excel Discussion (Misc queries) | |||
HELP! Filtering and sorting lists. | Excel Worksheet Functions | |||
Adding a KeyID column for sorting | New Users to Excel | |||
Comparing 2 Customer Lists to Identify Shared Customers | Excel Worksheet Functions |