Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare two file/colunms, hide row not does not equal list.
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare two file/colunms, hide row not does not equal list.
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". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jerry" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare two file/colunms, hide row not does not equal list.
Thanks!!!
"Max" wrote: 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". -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jerry" wrote: 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare two file/colunms, hide row not does not equal list.
welcome. Thanks for feeding back.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jerry" wrote in message ... Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare two text and if equal copy adjacent data | Excel Worksheet Functions | |||
Comparison of data in two separate colunms | Excel Discussion (Misc queries) | |||
Compare two dates and if they are equal | Excel Discussion (Misc queries) | |||
Pivot report - Hide Sums equal to zero | Excel Discussion (Misc queries) | |||
How to set SAVE AS file name to equal A1 contents when rename file | Excel Discussion (Misc queries) |