Compare two file/colunms, hide row not does not equal list.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2000 -have two files. File "A" has 1 through 4155 records (rows with
three columns) in numerical order. File "B", has a list of almost 200
rows/numbers (one column) from file "A". How can I hide the rows in file "A",
not are listed in file "B"?

Thanks, Jerry
 
Not sure whether you are still monitoring this post, Jerry.
Anyway, here's some thoughts ventured ..

Conceptually, you should be able to achieve this via setting up a helper col
and then autofiltering on the helper.

Let's start by simplifying the scenario by having both sheets in a single
file. A simple 10 seconds copy n paste of say, sheet: B into a new sheet of
File A where you have a master sheet: A would do it. Rename this new sheet
as: B.

In File A,

Assume that in your master sheet: A,
you have the key col (unique item#s for eg) listed in A2 down

In sheet: B,
assume the same key col is also col A, with item#s listed in A2 down

In sheet: A,

In an empty col to the right of data, say in col E,

Put in E2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B!A:A,0)),"Y","X"))

Copy E2 down to the last expected row of data in the key col A. Col E will
check col A for blank cells & match each item in col A with what's within col
A in sheet: B.

You'd get 3 possible results in col E: blanks: "", "Y", "X". Blanks will be
returned where there's nothing in col A to be compared (this will flag
missing values in-between within col A, if any, and also allows us to extend
the check to beyond the current filled range in col A). "Y" would be returned
for items in A found in B, "X" otherwise.

Now you could just apply autofilter on col E (Select entire col E, click
Data > Filter > Autofilter), and select as desired from the autofilter
droplist in E1. If you want to "hide" rows in A which are not listed in B,
just select: "Y" (as you mentioned in your posting). If you want to "show"
rows in A which are not found in B, just select: "X".
 
Thanks!!!

Max said:
Not sure whether you are still monitoring this post, Jerry.
Anyway, here's some thoughts ventured ..

Conceptually, you should be able to achieve this via setting up a helper col
and then autofiltering on the helper.

Let's start by simplifying the scenario by having both sheets in a single
file. A simple 10 seconds copy n paste of say, sheet: B into a new sheet of
File A where you have a master sheet: A would do it. Rename this new sheet
as: B.

In File A,

Assume that in your master sheet: A,
you have the key col (unique item#s for eg) listed in A2 down

In sheet: B,
assume the same key col is also col A, with item#s listed in A2 down

In sheet: A,

In an empty col to the right of data, say in col E,

Put in E2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B!A:A,0)),"Y","X"))

Copy E2 down to the last expected row of data in the key col A. Col E will
check col A for blank cells & match each item in col A with what's within col
A in sheet: B.

You'd get 3 possible results in col E: blanks: "", "Y", "X". Blanks will be
returned where there's nothing in col A to be compared (this will flag
missing values in-between within col A, if any, and also allows us to extend
the check to beyond the current filled range in col A). "Y" would be returned
for items in A found in B, "X" otherwise.

Now you could just apply autofilter on col E (Select entire col E, click
Data > Filter > Autofilter), and select as desired from the autofilter
droplist in E1. If you want to "hide" rows in A which are not listed in B,
just select: "Y" (as you mentioned in your posting). If you want to "show"
rows in A which are not found in B, just select: "X".
Click to expand...
 
Back
Top