Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheetfunctions,microsoft.public.excel.programming
|
|||
|
|||
Extract row based on a criteria
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. |
#2
Posted to microsoft.public.excel.worksheetfunctions,microsoft.public.excel.programming
|
|||
|
|||
Extract row based on a criteria
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. |
#3
Posted to microsoft.public.excel.worksheetfunctions,microsoft.public.excel.programming
|
|||
|
|||
Extract row based on a criteria
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 |
#4
Posted to microsoft.public.excel.worksheetfunctions,microsoft.public.excel.programming
|
|||
|
|||
Extract row based on a criteria
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) |
#5
Posted to microsoft.public.excel.worksheetfunctions,microsoft.public.excel.programming
|
|||
|
|||
Extract row based on a criteria
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
Extract multiple results based on multiple criteria | Excel Worksheet Functions | |||
Extract data on one spreadsheet to another based on criteria | New Users to Excel | |||
Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
Extract list of units based on error criteria to new list | Excel Worksheet Functions |