Filtering rows containing data from a list

Posted by Dan on December 11, 2000 2:29 PM

I am in charge of stores which are assigned a "Store number" i.e.
5265, 5103 etc. I would create a separate spreadsheet which contains a
master list of these store numbers for reference. When I receive a
spreadsheet which contains a column of store numbers throughout the
company, how can I create a macro/comand to delete all rows which do not
contain my store numbers?

Thanks for your help,
Dan.

Posted by marbel on December 11, 2000 3:38 PM

Here's one way:
Autofilter the list (data/autofilter) by your store.
Choose edit/go to / special / visible cells only
paste the results on a separate sheet, and you've got your resulting table of just your store.
good luck!
mb

Posted by Dan on December 11, 2000 6:23 PM

Thanks MB,
But, I have 96 locations and even the custom auto filter option will only give two variables.
My company has close to 700 different store numbers. I was hoping there would be a way to show
only the stores I have listed on another spreadsheet. Possible?

Posted by Celia on December 11, 2000 6:35 PM

If you want to retain one store only, then I think marbel's solution is the easiest.
However if you want to delete all stores except the ones on your list of stores, then one way of doing it (without a macro) :-

(1)I've assumed that the data you want to edit is on Sheet1 in Column A starting at A1, and your master list of stores is on Sheet2 starting at A1.
(2)On Sheet1 enter the following formula in row 1 of any column(say,column B) and fill it down to the end of the data :-
'=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$100,1)),"XXX","")
This should put XXX in column B for any store number not on the master list.
(3)Sort by column B so that all the XXX's are at the bottom
(4)Select the rows containing XXX and delete.
(5)Delete Column B

Celia