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,

Create a helper column C in sheet 2

C2: =COUNTIF(Sheet1!$A$2:$A$12,Sheet2!A3)0

AutoFilter all the FALSE Copy Paste Special Value OK


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

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,

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,

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,