selecting rows of data

  • Thread starter Thread starter elfeste
  • Start date Start date
E

elfeste

I have an excel worksheet with 4 columbs.

I want to automatically select multiple rows of data according to
search which will partly match one cell in the row and put the full ro
as results in a new worksheet.

for example

30/01/03,New York,233.85,complete
20/03/03,New Hampshire,188.22,complete
22/03/03,Texas,90.78,incomplete
23/04/03,Oregon,467.99,complete

If the search string is "New" how can I produce a new worksheet whic
will contain

30/01/03,New York,233.85,complete
20/03/03,New Hampshire,188.22,complete

Any suggestions would be appriciated
 
Assuming your data is all in single cells in Column A starting A2, then in
Column B in B2 put the following formula and copy down as far as needed

=IF(ISERROR(FIND("New",A2)),"","x")

Put a heading on Col B of say 'Valid', Then select all the data and use Data /
Filter / Autofilter and select the dropdown under valid and click on the x.
This will show all rows that meet your criteria.


Assuming your data is all in multiple cells per row starting A2 and finishing
say D2, then in Column E in E2 put the following formula and copy down as far as
needed

=IF(ISERROR(FIND("New",A2&B2&C2&D2)),"","x")

Put a heading on Col E of say 'Valid', Then select all the data and use Data /
Filter / Autofilter and select the dropdown under valid and click on the x.
This will show all rows that meet your criteria.

If you still want the data on another sheet then take a look at Data / Filter /
Advanced Filter in conjunction with the steps above. John has already given you
one link to Debra's site, so here is another to Advanced Filtering which will
let you output the results to another sheet if needed.

http://www.contextures.com/xladvfilter01.html
 
Back
Top