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
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(declined!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 <at>yahoo<dot>com
----
"WellsDesign" <[email protected]> wrote in message
news:[email protected]...
> 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=VLOOKUP(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(VLOOKUP(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" <[email protected]> wrote in message
news:[email protected]...
> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks