How to retrieve multiple rows with one criteria

[Guest]

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,

 

[Guest]

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

 

[Earl Kiosterud]

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...

 

[Debra Dalgleish]

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

 

[Guest]

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