Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
WellsDesign
 
Posts: n/a
Default 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
  #2   Report Post  
Gary Brown
 
Posts: n/a
Default

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

  #3   Report Post  
Max
 
Posts: n/a
Default

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



  #4   Report Post  
CLR
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 08:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"