Help ME!!!! Automatically extracting a filtered list

  • Thread starter Thread starter Iven
  • Start date Start date
I

Iven

Is it possble to extract a list of cells containing part of a word to a
different sheet? :confused:

Exaple: I have a very long list containing items similar to the
following

Media
video cassette TDK, Box of 10
video cassette SONY, Box of 5
video cassette cleaner, Single units

Digital Media
DVD -R, Pack of 25
DVD +R, Pack of 10
DVD R/W, Pckof 5

Now I ned to extract to a different excel sheet wherever there is
written "video" and extract also to an other different sheet wherever
there is written "DVD". I do no want to display the words Media and
Digital Media and no blank cells.

Is this possible? Can anyone help me?
I need to do this automatically and not using manual filterng options.

Thans in advance.
'Send me an email if you can help me.' ([email protected])
 
Try this:

Assume your list is in Sheet1, col A, row1 down

In say, Sheet2:
------------
Put in A1: =IF(ISERROR(SEARCH("*video*",Sheet1!A1)),0,Sheet1!A1)

Copy A1 down col A as many rows as there is data in Sheet1

Select col A

Click Data > Sort
Select "No header" > Sort by: Column A > Descending > OK

(This will throw all the text to the top and the zeros below)

Do a copy > paste special > values *in-place*
on col A to freeze the values

-----------------
Repeat the same steps for DVD in say, Sheet3
------------------
In Sheet3:

Put in A1: =IF(ISERROR(SEARCH("*DVD*",Sheet1!A1)),0,Sheet1!A1)

Copy A1 down col A as many rows as there is data in Sheet1

Select col A

Click Data > Sort
Select "No header" > Sort by: Column A > Descending > OK

(This will throw all the text to the top and the zeros below)

Do a copy > paste special > values *in-place*
on col A to freeze the values
 
I'd apply data|filter|autofilter to your range.

Then using the filter over the description(?) column, use Custom. Then Contains
DVD. Then copy the visible rows from that sheet to a new sheet.

And if the original list got changed, I'd delete the old worksheets and create
them again.
 
Thanks Max, now I managed to work with your help.

My issue is that the sheet from where I am getting the data change
frequently and so there is always some sort of manual intervention.
wanted to reduce it.
Now what I have to do is important daily this sheet and sort the row
so that the text is always at the top.

Thank again.
Iven.
 
Back
Top