Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to retrieve multiple rows with one criteria
Hi,
I have more than 1000 rows of data and the very first column has more than 100 values (i.e. Pencil shows in Cell A2, A18, A95, A357 and a number, 100, shows in Cell A5, A58, A754, A951 and the rest of values has similar entries in different cells in the first column. I am trying to get the all the rows of data with Pencil in the first column when I type Pencil in a cell. Vlookup doesn't work since it only takes the very first cell that has Pencil. Filter doesn't work well since there are two many values to choose from. How do I set up a formula that retrieves a multiples rows of data with the same one criteria in the first column? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to retrieve multiple rows with one criteria
Create yourself a new "Helper-Column" Titled "Select?" (say M4) and in (1st
data row = 5) enter =IF(A5="Pencil","S","") and Copy down all 1000 + rows. Then Auto_Filter on Column M selecting only S "James" wrote: Hi, I have more than 1000 rows of data and the very first column has more than 100 values (i.e. Pencil shows in Cell A2, A18, A95, A357 and a number, 100, shows in Cell A5, A58, A754, A951 and the rest of values has similar entries in different cells in the first column. I am trying to get the all the rows of data with Pencil in the first column when I type Pencil in a cell. Vlookup doesn't work since it only takes the very first cell that has Pencil. Filter doesn't work well since there are two many values to choose from. How do I set up a formula that retrieves a multiples rows of data with the same one criteria in the first column? Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to retrieve multiple rows with one criteria
James,
Use Autofilter or Advanced filter. -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "James" wrote in message ... Hi, I have more than 1000 rows of data and the very first column has more than 100 values (i.e. Pencil shows in Cell A2, A18, A95, A357 and a number, 100, shows in Cell A5, A58, A754, A951 and the rest of values has similar entries in different cells in the first column. I am trying to get the all the rows of data with Pencil in the first column when I type Pencil in a cell. Vlookup doesn't work since it only takes the very first cell that has Pencil. Filter doesn't work well since there are two many values to choose from. How do I set up a formula that retrieves a multiples rows of data with the same one criteria in the first column? Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to retrieve multiple rows with one criteria
You could do this with programming and an advanced filter or AutoFilter.
There's a sample there that uses and Advanced Filter to create a list when a criterion is typed in a cell: http://www.contextures.com/excelfiles.html Under Filters, look for FL0002 - Product List by Week Number James wrote: Hi, I have more than 1000 rows of data and the very first column has more than 100 values (i.e. Pencil shows in Cell A2, A18, A95, A357 and a number, 100, shows in Cell A5, A58, A754, A951 and the rest of values has similar entries in different cells in the first column. I am trying to get the all the rows of data with Pencil in the first column when I type Pencil in a cell. Vlookup doesn't work since it only takes the very first cell that has Pencil. Filter doesn't work well since there are two many values to choose from. How do I set up a formula that retrieves a multiples rows of data with the same one criteria in the first column? Thanks, -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to retrieve multiple rows with one criteria
You could (to make it simplier) make Cell M1 the Input Cell for what you want
to Select at any point-in-time. You'll have to change the formula to: =IF(A5=$M$1,"S","") ' and Copy down replacing previous formula Hope this helps... Jim "JMay" wrote: Create yourself a new "Helper-Column" Titled "Select?" (say M4) and in (1st data row = 5) enter =IF(A5="Pencil","S","") and Copy down all 1000 + rows. Then Auto_Filter on Column M selecting only S "James" wrote: Hi, I have more than 1000 rows of data and the very first column has more than 100 values (i.e. Pencil shows in Cell A2, A18, A95, A357 and a number, 100, shows in Cell A5, A58, A754, A951 and the rest of values has similar entries in different cells in the first column. I am trying to get the all the rows of data with Pencil in the first column when I type Pencil in a cell. Vlookup doesn't work since it only takes the very first cell that has Pencil. Filter doesn't work well since there are two many values to choose from. How do I set up a formula that retrieves a multiples rows of data with the same one criteria in the first column? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to retrieve multiple values in multiple rows with one criteria | Excel Discussion (Misc queries) | |||
retrieve multiple results with one criteria... | Excel Discussion (Misc queries) | |||
Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
Summing Rows with Multiple Criteria | Excel Worksheet Functions | |||
How can I retrieve multiple rows that match one criteria, i.e. a d | Excel Worksheet Functions |