Show Data In Range not appearing in Separate Range
 

Excel 2003

Good morning Everybody,

I need assistance to generate a formula or macro based on example data below
I have 2 priorly constructed worksheets that I was asked to work with.

For simplicity and example purposes, names are SheetA and SheetB,
spacing is inconsistent and data is non-sequential.

___________

Example Sheet A

Col A
Projects

BB2029
PARS
ABSB
BB3500
PARS
Insp
BMM144
PARS
ANA

______________
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:
Compare Project names in Sheet A to Project names on sheet B:

If the Project Name
on Sheet B is found on
Sheet A, then On Sheet B hide the data row
End result on Sheet B is a list of project names and data for all projects
not listed on Sheet A.

Sheet B Output for this example:
Col A Col B
SR2000 20
AS2044 200
MM2000 355
RN1440 -

Thanks Much,

Show Data In Range not appearing in Separate Range
 

How about an alternative?

Use a helper cell that contains a formula that returns whether the data is on
the other worksheet. Then apply data|filter|autofilter to that range so that
you could hide/show what you want.

If you want to try:

Insert a new column (I'd use a new column A).

Then in A2 of SheetB (headers in row 1???):

=isnumber(match(b2,'sheetA'!a:a,0))
and drag down as far as your data goes.

I'd put this in A1 (as a header):
On SheetA



Brent E wrote:

Excel 2003

Good morning Everybody,

I need assistance to generate a formula or macro based on example data below
I have 2 priorly constructed worksheets that I was asked to work with.

For simplicity and example purposes, names are SheetA and SheetB,
spacing is inconsistent and data is non-sequential.

___________

Example Sheet A

Col A
Projects

BB2029
PARS
ABSB
BB3500
PARS
Insp
BMM144
PARS
ANA

______________
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:
Compare Project names in Sheet A to Project names on sheet B:

If the Project Name
on Sheet B is found on
Sheet A, then On Sheet B hide the data row
End result on Sheet B is a list of project names and data for all projects
not listed on Sheet A.

Sheet B Output for this example:
Col A Col B
SR2000 20
AS2044 200
MM2000 355
RN1440 -

Thanks Much,

--

Dave Peterson

Show Data In Range not appearing in Separate Range
 

Thanks for the good idea Dave, I will try that. I appreciate your prompt
assistance.

I don't mind using the extra cell and I think the IsNumber and Match
functions may be what I need. Will this formula return a true or false value?
Thanks.

"Dave Peterson" wrote:

How about an alternative?

Use a helper cell that contains a formula that returns whether the data is on
the other worksheet. Then apply data|filter|autofilter to that range so that
you could hide/show what you want.

If you want to try:

Insert a new column (I'd use a new column A).

Then in A2 of SheetB (headers in row 1???):

=isnumber(match(b2,'sheetA'!a:a,0))
and drag down as far as your data goes.

I'd put this in A1 (as a header):
On SheetA



Brent E wrote:

Excel 2003

Good morning Everybody,

I need assistance to generate a formula or macro based on example data below
I have 2 priorly constructed worksheets that I was asked to work with.

For simplicity and example purposes, names are SheetA and SheetB,
spacing is inconsistent and data is non-sequential.

___________

Example Sheet A

Col A
Projects

BB2029
PARS
ABSB
BB3500
PARS
Insp
BMM144
PARS
ANA

______________
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:
Compare Project names in Sheet A to Project names on sheet B:

If the Project Name
on Sheet B is found on
Sheet A, then On Sheet B hide the data row
End result on Sheet B is a list of project names and data for all projects
not listed on Sheet A.

Sheet B Output for this example:
Col A Col B
SR2000 20
AS2044 200
MM2000 355
RN1440 -

Thanks Much,

--

Dave Peterson

Show Data In Range not appearing in Separate Range
 

That worked Great.

Thanks for your assistance and have a great evening.

"Dave Peterson" wrote:

How about an alternative?

Use a helper cell that contains a formula that returns whether the data is on
the other worksheet. Then apply data|filter|autofilter to that range so that
you could hide/show what you want.

If you want to try:

Insert a new column (I'd use a new column A).

Then in A2 of SheetB (headers in row 1???):

=isnumber(match(b2,'sheetA'!a:a,0))
and drag down as far as your data goes.

I'd put this in A1 (as a header):
On SheetA



Brent E wrote:

Excel 2003

Good morning Everybody,

I need assistance to generate a formula or macro based on example data below
I have 2 priorly constructed worksheets that I was asked to work with.

For simplicity and example purposes, names are SheetA and SheetB,
spacing is inconsistent and data is non-sequential.

___________

Example Sheet A

Col A
Projects

BB2029
PARS
ABSB
BB3500
PARS
Insp
BMM144
PARS
ANA

______________
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:
Compare Project names in Sheet A to Project names on sheet B:

If the Project Name
on Sheet B is found on
Sheet A, then On Sheet B hide the data row
End result on Sheet B is a list of project names and data for all projects
not listed on Sheet A.

Sheet B Output for this example:
Col A Col B
SR2000 20
AS2044 200
MM2000 355
RN1440 -

Thanks Much,

--

Dave Peterson