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

E

Ed

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.
 
M

Max

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
 
A

Aladin Akyurek

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.
 
B

Bruno Campanini

Max said:
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
Click to expand...

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
 
J

John

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
----------------------------------------
 
A

Aladin Akyurek

You could concatenate items of each list in a new range and adapt the
formulas system to the new ranges.
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
Click to expand...

--

[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.
 
Top