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,

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,

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,

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

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,