I have a large worksheet like Table-1 with few thousands rows.
Occasionally I need to create a subset of the data like the Table-2.

Table-1:
A B C
1 Item Department Amount
2 10 X 45
3 20 Y 23
4 30 X 65
5 20 X 23
6 20 Z 28
7 50 X 44
8 20 X 32
9 60 Y 55

Table-2:
Item Department Amount
20 Y 23
20 X 23
20 Z 28
20 X 32

How do I automate the extraction of rows (based on an Item) from the
Table-1 to produce an output like the Table-2?

Thanks for any suggestion of function, formula or simple macro.

Sincerely
Mah.

Mah,

Take a look at "AutoFilter"
Some help and examples can be found he
http://www.contextures.com/xlautofilter01.html

John

"Mah" wrote in message
om...
I have a large worksheet like Table-1 with few thousands rows.
Occasionally I need to create a subset of the data like the Table-2.

Table-1:
A B C
1 Item Department Amount
2 10 X 45
3 20 Y 23
4 30 X 65
5 20 X 23
6 20 Z 28
7 50 X 44
8 20 X 32
9 60 Y 55

Table-2:
Item Department Amount
20 Y 23
20 X 23
20 Z 28
20 X 32

How do I automate the extraction of rows (based on an Item) from the
Table-1 to produce an output like the Table-2?

Thanks for any suggestion of function, formula or simple macro.

Sincerely
Mah.

I'd just select the range and do Data|filter|Autofilter

then show the items you want
(and copy them to a new sheet if really necessary)

Mah wrote:

I have a large worksheet like Table-1 with few thousands rows.
Occasionally I need to create a subset of the data like the Table-2.

Table-1:
A B C
1 Item Department Amount
2 10 X 45
3 20 Y 23
4 30 X 65
5 20 X 23
6 20 Z 28
7 50 X 44
8 20 X 32
9 60 Y 55

Table-2:
Item Department Amount
20 Y 23
20 X 23
20 Z 28
20 X 32

How do I automate the extraction of rows (based on an Item) from the
Table-1 to produce an output like the Table-2?

Thanks for any suggestion of function, formula or simple macro.

Sincerely
Mah.

--

Dave Peterson

Thank you Dave & John for your suggestions on using Filter.
However what I'm looking for is something that could create the subset
list automatically based on an Item value (say 20). Then I could just
change the Item value to get any subset list.



Dave Peterson wrote in message ...
I'd just select the range and do Data|filter|Autofilter

then show the items you want
(and copy them to a new sheet if really necessary)

You may want to run a macro whenever you need the extraction.

Debra Dalgleish's has some code that does this kind of thing:

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

There are a couple of files you may want to steal from:

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Mah wrote:

Thank you Dave & John for your suggestions on using Filter.
However what I'm looking for is something that could create the subset
list automatically based on an Item value (say 20). Then I could just
change the Item value to get any subset list.

Dave Peterson wrote in message ...
I'd just select the range and do Data|filter|Autofilter

then show the items you want
(and copy them to a new sheet if really necessary)


--

Dave Peterson