This works.... i will just modify it for a single search also. Does having
about 50 of these functions hurt the running process of excel?
Also is it possible to reference the A1:A1000... I don't want to have to
edit the functions... would it be wrong to use a number that would be way
higher than I'd ever use or even search the whole column? I just don't want
excel to slow to a crawl!

Your example is one of the criteria.
Another is searching column A for ? then multiplying e*d*c
Some others would be searching A & B like your example then adding columns C
& D

You've put me in the right direction though!!

Thanks Again!
Craig



"keepITcool" wrote in message
.com...


Then tell us what the criteria are..

Basically you could use the sunmproduct function
SUMPRODUCT(--(a1:a1000=100),--(b1:b1000=50),c1:c1000,d1:d1000,e1:e1000)

if you CAN make a table for the result like
50 60 70
100
101
102

THEN you can use data/table (see excel help for this oft forgotten gem)

set calculation to automatic EXCEPT tables, and you should get
controlled performance.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Craig wrote :

Using a pivot table would work but I need to extract about 50
separate searches... would be too time consuming!
I need an automated way to do this.
Craig


"Jim Thomlinson" wrote in
message ...
Have you tired using a pivot table? That would be ideal for what
you want to
do. Place your cursor in one cell in the middle of the data. Select
Data -
Pivot Table...

Follow the wizard (You can probably just select finish and let
Excel make all the guesses for you). Drag the column headings onto
the table that was just created and you are off to the races...

HTH

"Craig" wrote:

Hi there, I'm wondering which would be the most eficient way to
search for
and extract data from anywhere from 1000 to 2000 rows of data.
Would the use
of a worksheet function sufice or would VBA code be better?
Here's the senerio:

A B C D E F G
....... 1 101 204 $5.99 12 1 ? ?
2 104 201 $4.99 10 2 ? ?
3 101 204 $2.99 3 4 ? ?
4 105 202 $1.99 11 3 ? ?
5 102 200 $4.59 20 5 ? ?
6 102 200 $1.50 30 1 ? ?
7 101 208 $6.49 25 1 ? ?
ETC...
I need to extract information in many ways.... i will need to do
about 50 separate and different searches and extracts from this
data to a specific cell. So I'm thinking that the re-calculating
of this workbook would be too
demanding. Here are two examples of what I'm looking for... The
first is searching through column A til the end (End is always
unknown and changing)
let's say we're searching for 101 in column A. It would find 3
results, in
this case. I would then need the sum from columns(E*D*C) from rows
1,3,7. So
cell AA100 would get this grand total of $270.01.
The second method would be the same but would perform a second
search in column B... so we might look for 101 in column A and 208
in column B then do
the same mathematics. I hope I've explained this OK... I think I'm
confused!

Thanks Craig