filter and keep same information.
 

I have a list of 3000 names from 3 diffrent lists. (Accpted, declined, open)
I want to get a list of all the declined names that also appear on the open
or won lists. how do I do this?
Using Excel 2002

Use the vlookup function to check if the name in the open list is also in the
accepted list.
HTH,
Gary Brown


"WellsDesign" wrote:

I have a list of 3000 names from 3 diffrent lists. (Accpted, declined, open)
I want to get a list of all the declined names that also appear on the open
or won lists. how do I do this?
Using Excel 2002

Another play to try ..

Assuming the names are in col A, from A1 down
in all 3 sheets named: Accpted, declined, open

In a new sheet:
-----------------
Put in B1:

=IF(OR(ISNUMBER(MATCH(declined!A1,Accpted!A:A,0)), ISNUMBER(MATCH(declined!A1
,open!A:A,0))),ROW(),"")

Copy B1 down as many rows as there are
names in "declined"'s col A, say down to B1000
(i.e. if the last name in "declined" is in A1000)

Put in A1:

=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(dec lined!A:A,MATCH(SMALL(B:B,
ROWS($A$1:A1)),B:B,0)))

Copy A1 down to A1000
(cover the same range as the formula fill in col B)

Col A will return the desired results

Just copy and paste special as values
either in-situ or elsewhere

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WellsDesign" wrote in message
...
I have a list of 3000 names from 3 diffrent lists. (Accpted, declined,
open)
I want to get a list of all the declined names that also appear on the
open
or won lists. how do I do this?
Using Excel 2002

Assuming your "Accepted" list is in column A,
your "Declined" list in column B, and your "Open" list in column C........

put this in D1 and copy down.......
=IF(ISNA(A1=VLOOKUP(A1,declined,1,FALSE)),"",IF(A1 =VLOOKUP(A1,declined,1,FAL
SE),"dup in declined",""))
this will tell you if any column A entry also shows on column B

put this in E1 and copy down........
=IF(ISNA(A1=VLOOKUP(A1,open,1,FALSE)),"",IF(A1=VLO OKUP(A1,open,1,FALSE),"dup
in open",""))
this will tell you is any column A entry also shows on column C

put this in F1 and copy down.........
=IF(OR(ISNA(VLOOKUP(A1,declined,1,FALSE)),ISNA(VLO OKUP(A1,open,1,FALSE))),""
,IF(VLOOKUP(A1,declined,1,FALSE)=VLOOKUP(A1,open,1 ,FALSE),"trip in all
three",""))
this will tell you if any column A entry appears in BOTH columns B and
C........

Note: formulas are all on one line, watch out for email word wrap.

Vaya con Dios,
Chuck, CABGx3


"WellsDesign" wrote in message
...
I have a list of 3000 names from 3 diffrent lists. (Accpted, declined,
open)
I want to get a list of all the declined names that also appear on the
open
or won lists. how do I do this?
Using Excel 2002