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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 04 Sep 2005 00:28 GMT

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.

Reply to this Message

Max - 04 Sep 2005 01:16 GMT

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

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

Reply to this Message

Bruno Campanini - 04 Sep 2005 13:28 GMT

> One way ..
>
[quoted text clipped - 13 lines]
> 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

Reply to this Message

Aladin Akyurek - 04 Sep 2005 07:01 GMT

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.

Reply to this Message

John - 10 Sep 2005 11:29 GMT

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

> 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.
[quoted text clipped - 32 lines]
> > 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.

Reply to this Message

Aladin Akyurek - 12 Sep 2005 09:48 GMT

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:-
[quoted text clipped - 42 lines]
>>>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.

Signature

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

Reply to this Message