Best way to search/filter a column?

  • Thread starter Thread starter Kobayashi
  • Start date Start date
K

Kobayashi

I have a s/sheet that contains in excess of 1 thousand rows. Column A
has an index I wish to use to sort and remove data and this contains
approx. 100 unique strings, of which I wish to search for approx. 30
and delete the rows that do not match any of the 30.

So, does anybody know the best way to do this?

Autofilter?
For each.. and multiple If statements?
For... and multiple If statements?
Select case statements?

I'm happy to try and work the code out (although if anybody has any to
hand that would be even better!) but just don't want to waste time
creating slow and inefficient code to start with so if anybody could
tell me which method is the best I would much appreciate it?

Regards,

Adrian
 
Adrian,

I'd use the Advanced Filter. For the criteria range,
specify a range containing the list of strings you want to
filter for with the field name as the first row.

To automate this, use the macro recorder while you do it.

Cheers,
Dave
 
Please, I'm only looking for direction as to which method to follow?
I'll do the rest.

Thanks,

Adrian
 
Dave, thanks but could you take a look at my releated post above and see
if you can help?

Regards,

Adrian
 
How about putting that list in another worksheet.

then insert a new column that looks to see if the adjacent cell matches that
list.

=if(isnumber(match(b1,sheet2!a:a,0)),"Match","no Match")
and fill down.

(I'm not sure if the list will be the keep list or the delete list.)

Then sort your data by that column, apply data|filter|autofilter and delete the
ones you want. Then delete that helper column.

If your rows to be deleted are contiguous, then the delete will work quicker.
 
Dave,

Many thanks. I've tinkered and, taking your advice, got things
working.

Thanks again,

Adrian
 
Back
Top