selecting rows of data

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

--
Message posted from http://www.ExcelForum.com

0
12/31/2003 7:36:32 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
438 Views

Similar Articles

[PageSpeed] 49

elfeste,

Sounds like an Autofilter would do the trick.
Take a look here for some tips and examples:

http://www.contextures.com/xlautofilter01.html

John

"elfeste" <elfeste.zbh0m@excelforum-nospam.com> wrote in message
news:elfeste.zbh0m@excelforum-nospam.com...
> I have an excel worksheet with 4 columbs.
>
> I want to automatically select multiple rows of data according to a
> search which will partly match one cell in the row and put the full row
> 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 which
> will contain
>
> 30/01/03,New York,233.85,complete
> 20/03/03,New Hampshire,188.22,complete
>
> Any suggestions would be appriciated.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
jwilson (359)
12/31/2003 8:40:07 PM
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

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
 Seasons Greetings and Very Best wishes to all  :-)
----------------------------------------------------------------------------



"elfeste" <elfeste.zbh0m@excelforum-nospam.com> wrote in message
news:elfeste.zbh0m@excelforum-nospam.com...
> I have an excel worksheet with 4 columbs.
>
> I want to automatically select multiple rows of data according to a
> search which will partly match one cell in the row and put the full row
> 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 which
> will contain
>
> 30/01/03,New York,233.85,complete
> 20/03/03,New Hampshire,188.22,complete
>
> Any suggestions would be appriciated.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003


0
ken.wright (2489)
1/1/2004 2:39:35 PM
Reply:

Similar Artilces: