Searching & Extracting Data
 

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

Searching & Extracting Data
 

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

Searching & Extracting Data
 

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

Searching & Extracting Data
 

The problem that I see is that with having 50 different criteria you are
going to have to come up with 50 different ways of slicing and dicing this.
You will have to create 50 different procedure calls to make this happen. If
that is do-able then I would recomend using the find / find next function and
adding in an extra column that is the concatenation of the first two
columns... If you want to try that let me know and I can help get you going...

HTH

"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

Searching & Extracting Data
 

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

Searching & Extracting Data
 

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