Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching data | Excel Discussion (Misc queries) | |||
Searching for data | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
extracting data from a spreadsheet by searching on columns | New Users to Excel | |||
Searching Data | Excel Programming |