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.

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.