list 1 has 400 names List 2 has 4000. find manes from list 1 on 2
 

I need help comparing 2 lists in excel. Here is my goal; I have one list with
4500 names and one list with 400 names. I would like to find out which of the
400 names is on the 4500 name list. how do I do it? thanks for your help.

One way ..

Assume the 2 lists are in cols A and B, in row1 down with
col A housing the 4500 names, col B containing the 400 names

Put in C1:
= IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),"") )

Put in D1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(B:B ,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select C1:D1, copy down to D400

Col D will return all the names in the 400 list in col B which is found
within the 4500 list in col A, neatly bunched at the top, with blank rows
below

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ed" wrote in message
...
I need help comparing 2 lists in excel. Here is my goal; I have one list
with
4500 names and one list with 400 names. I would like to find out which of
the
400 names is on the 4500 name list. how do I do it? thanks for your help.

Let column A from A3 on house the longer list and column B from B3 on
the shorter list, with headers List1 and List2 in A2:B2.

C1: 0

which is mandatory.

C2: Idx

which is a header.

C3, copied down:

=IF((B3<"")*ISNUMBER(MATCH(B3,$A$3:$A$4503,0)),LO OKUP(9.99999999999999E+307,$C$1:C2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C1:C403)

D2: New List

which is just a header.

D3, copied down:

=IF(ROWS($D$3:D3)<=$D$1,LOOKUP(ROWS($D$3:D3),$C$3: $C$403,$B$3:$B$403),"")

The New List will have not have any blank records in between its first
and last items.

Note that the foregoing formula system is correct, efficient (that is:
fast), and robust.

Ed wrote:
I need help comparing 2 lists in excel. Here is my goal; I have one list with
4500 names and one list with 400 names. I would like to find out which of the
400 names is on the 4500 name list. how do I do it? thanks for your help.

"Max" wrote in message
...
One way ..

Assume the 2 lists are in cols A and B, in row1 down with
col A housing the 4500 names, col B containing the 400 names

Put in C1:
= IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),"") )

Put in D1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(B:B ,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select C1:D1, copy down to D400

Col D will return all the names in the 400 list in col B which is found
within the 4500 list in col A, neatly bunched at the top, with blank rows
below

Or, with one single formula,
having 4500 names in Ra1, 400 names in Ra2:

{=IF(ISERROR(INDEX(Ra1,SMALL(IF(ISNA(MATCH(Ra2,Ra1 ,0)),
"",MATCH(Ra2,Ra1,0)),ROW(A1)))),"",INDEX(Ra1,
SMALL(IF(ISNA(MATCH(Ra2,Ra1,0)),
"",MATCH(Ra2,Ra1,0)),ROW(A1))))}
FormulaArray aside the first row of Ra2, then to be copied
alongside Ra2.

Bruno

If I wanted to check that the Surname AND the Firstnames were the same in each of four columns,
what changes would I make? Ideally I would wwish that the output was as below:-

Column A Column B Column C Column D
Surname Forename Common entry Surname Common entry Forname
Wilson Bob Wilson Bob



Jonah
----------------------------------------
Aladin Akyurek wrote:

Let column A from A3 on house the longer list and column B from B3 on
the shorter list, with headers List1 and List2 in A2:B2.

C1: 0

which is mandatory.

C2: Idx

which is a header.

C3, copied down:

=IF((B3<"")*ISNUMBER(MATCH(B3,$A$3:$A$4503,0)),LO OKUP(9.99999999999999E+307,$C$1:C2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C1:C403)

D2: New List

which is just a header.

D3, copied down:

=IF(ROWS($D$3:D3)<=$D$1,LOOKUP(ROWS($D$3:D3),$C$3: $C$403,$B$3:$B$403),"")

The New List will have not have any blank records in between its first
and last items.

Note that the foregoing formula system is correct, efficient (that is:
fast), and robust.

Ed wrote:
I need help comparing 2 lists in excel. Here is my goal; I have one list with
4500 names and one list with 400 names. I would like to find out which of the
400 names is on the 4500 name list. how do I do it? thanks for your help.

You could concatenate items of each list in a new range and adapt the
formulas system to the new ranges.

John wrote:
If I wanted to check that the Surname AND the Firstnames were the same in each of four columns,
what changes would I make? Ideally I would wwish that the output was as below:-

Column A Column B Column C Column D
Surname Forename Common entry Surname Common entry Forname
Wilson Bob Wilson Bob



Jonah
----------------------------------------
Aladin Akyurek wrote:


Let column A from A3 on house the longer list and column B from B3 on
the shorter list, with headers List1 and List2 in A2:B2.

C1: 0

which is mandatory.

C2: Idx

which is a header.

C3, copied down:

=IF((B3<"")*ISNUMBER(MATCH(B3,$A$3:$A$4503,0)), LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C1:C403)

D2: New List

which is just a header.

D3, copied down:

=IF(ROWS($D$3:D3)<=$D$1,LOOKUP(ROWS($D$3:D3),$C$ 3:$C$403,$B$3:$B$403),"")

The New List will have not have any blank records in between its first
and last items.

Note that the foregoing formula system is correct, efficient (that is:
fast), and robust.

Ed wrote:

I need help comparing 2 lists in excel. Here is my goal; I have one list with
4500 names and one list with 400 names. I would like to find out which of the
400 names is on the 4500 name list. how do I do it? thanks for your help.



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.