Posted to microsoft.public.excel.misc
|
|
Filter Range on Sheet B Based on List on Sheet A
Thanks to u both. I'll try those and let u know what I find out. I appreciate
your assistance.
"Max" wrote:
Here's one formulas play to drive out
the required exclusions listing in a new sheet: C.
Illustrated in this sample construct:
http://www.savefile.com/files/655843
Compare B vs A n filter exclusions in C.xls
Source data is assumed in sheets: A, B
in cols A and B, with data running in row2 down
In a new sheet: C,
In A2:
=IF(B!A2="","",IF(ISNUMBER(MATCH(B!A2,A!A:A,0)),"" ,ROW()))
In B2:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(B!A:A,SMALL($A:$ A,ROW(A1))))
Copy B2 to C2. Select A2:C2, copy down to cover the max expected extent of
data in B's col A. Hide away col A. Cols B & C will return the required
exclusion results, ie lines in B not found in A, all neatly bunched at the
top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brent E" wrote:
Excel 2003
Good evening Everybody,
I need assistance to generate a formula or macro
Basically what I am trying to do is to set up a filter that will list
projects on Sheet B
that aren't listed on Sheet A
I have 2 priorly constructed worksheets that I was asked to work with.
For example purposes, names are SheetA and SheetB,
spacing is inconsistent and data is non-sequential.
___________
Example Sheet A
Col A Col B
Projects Apr
BB2029
PARS 20
ABSB 90
BB3500
PARS -
Bud 300
Insp 80
PB -
BMM144
PARS 9
ANA 35
______________
Example Sheet B
Col A Col B
Projects Apr
BB2029 350
SR2000 20
BB3500 -
AS2044 200
MM2000 355
BMM144 900
RN1440 -
I need a forumula or macro
that will:
For each Project name
on Sheet B: ColA,
Search for same Project on sheet A: ColA
If the Project Name
from Sheet B is found on
Sheet A, then
On Sheet B, hide that data row.
Sheet B Output for this example:
Col A Col B
SR2000 20
AS2044 200
MM2000 355
RN1440 -
Thanks Much,
|