Extracting data from a large worksheet using 1 variable

[Dano]

My goal is to easily extract a subset of a large data file from on-line surveys (1,000's of lines of data).

For instance: All raw data is in one workbook, in one worksheet, one column of which includes a "client code." A second workbook links to this sheet. Entering a "client code" here would copy all related data into a worksheet in the second file (i.e., any row of data in the first file that meets this criteria is copied into the second file for analysis). I'd prefer not to modify the raw data, and most subsets contain only 10 to 40 lines of data.

I've tried using simple formulas, but this results in huge numbers of calculations and slow performance.

Any suggestions would be GREATLY appreciated.

[Richard Buttrey]

Have you tried using Data-->Filter--Advanced Filter?
That's the standard way of analysing large data sets, either by filtering it in place, or extracting a sub-set based on your client code.

[stevekirk]

hi dano,
welcome to the forum

why do you need two workbooks
it would be a lot easier in one.
if you use only one there are many ways to get the data as you require it
and it would be a lot faster

steve

[Dano]

Steve,

The reason I'm using 2 workbooks is to simplify updating the raw data (i.e., when we download an updated raw data set, we simply replace the first file). I found that cutting and pasting repeatedly resulted in some equation errors. Hope that makes sense.

Also, I tried Advanced Filters, which work, except that I'd like to automate the process so that a user can simply enter the variable to sort for in a cell rather than going through the Advanced Filters steps.

Any other ideas?

[davesexcel]

My goal is to easily extract a subset of a large data file from on-line surveys (1,000's of lines of data).

For instance: All raw data is in one workbook, in one worksheet, one column of which includes a "client code." A second workbook links to this sheet. Entering a "client code" here would copy all related data into a worksheet in the second file (i.e., any row of data in the first file that meets this criteria is copied into the second file for analysis). I'd prefer not to modify the raw data, and most subsets contain only 10 to 40 lines of data.

I've tried using simple formulas, but this results in huge numbers of calculations and slow performance.

Any suggestions would be GREATLY appreciated.

Is this something you might be looking for?
Check out the sample workbook at this thread
http://www.excelforum.com/showthread.php?t=631965

also check out this sample workbook from this thread
http://www.excelforum.com/showthread.php?t=631657

of course we would have to edit it to suite your requirements....

[Dano]

Dave,

Your second example is EXACTLY what I'm trying to do (the "Filter Veggies" example). Can it be set to reference data in a second workbook?

Here's the scary part... Although I've worked with Excel for years, I have yet to work with Macros or VBA. Any suggestions of a very basic resource to get me started so I can begin understanding what you did here?

I REALLY appreciate your help. I've been thinking about this for weeks! I should have joined this forum much sooner.

Dan

[davesexcel]

Dave,

Your second example is EXACTLY what I'm trying to do (the "Filter Veggies" example). Can it be set to reference data in a second workbook?

Here's the scary part... Although I've worked with Excel for years, I have yet to work with Macros or VBA. Any suggestions of a very basic resource to get me started so I can begin understanding what you did here?

I REALLY appreciate your help. I've been thinking about this for weeks! I should have joined this forum much sooner.

Dan

Well I think I did it,
open both workbooks,
select the workbook GetFromVegies and use the dropdown to pick a vegie
it will filter the other workbook and paste the results in GetFromVegies,
Good Luck...........

[Dano]

Dave,

I'm hoping you can help me out one more time. I've tried to adapt your solution to my program, but I may have messed up the code in the process (hey, I'm only 12 hours into VBA coding... what do you expect). Here's a quick summary of the attached 2 files:

1) The raw data will always be on "Sheet1" of a file titled "Sheet_1.xls" (I know the workbook title is confusing, but that's what downloads from our survey site). This can contain up to several thousand lines of data. The filtered sets will typically be less than 150 lines of data.

2) "OES.program.xls" is the primary workbook with the filter on "Select Data" sheet.

3) The variable that we're filtering for is in Column I of the data file (Sheet_1.xls)

4) The column range of data to be copied is A:IT (254 columns)

I don't think I correctly updated the workbook names or reference cell ranges correctly in the code. The macro also seems to be verifying the filter entry based on a list which I think I need to disable... I can't find this in the code.

Thanks again for your help and patience Dave!!!

Dan

[Dano]

Well I think I did it,
open both workbooks,
select the workbook GetFromVegies and use the dropdown to pick a vegie
it will filter the other workbook and paste the results in GetFromVegies,
Good Luck...........

Thanks Dave. Could you please take a look at my last post to this thread. I forgot to "Quote" you so I'm not sure it was flagged for you. I am really excited that this might actually work.

[davesexcel]

Try This,
Open both workbooks,
Click the button to select items to filter,
The selected item will end up in I1 as you said that is the column to filter by..

[Dano]

Try This,
Open both workbooks,
Click the button to select items to filter,
The selected item will end up in I1 as you said that is the column to filter by..

Dave,

Regardless of what I select, the macro returns "no data to copy." I do have both workbooks open. Any thoughts? Also, is "Sheet1" in the main workbook used for this routine (I'm trying to follow your code)?

Dan

BTW - This looks very professional... really nice job!!

[davesexcel]

Dave,

Regardless of what I select, the macro returns "no data to copy." I do have both workbooks open. Any thoughts? Also, is "Sheet1" in the main workbook used for this routine (I'm trying to follow your code)?

Dan

BTW - This looks very professional... really nice job!!

Very interesting, I tried it and it did as you said, had something to do with the clear contents
Here's an updated version
Filter Examples.zip

I will be removing the original

I have downloaded it and it seems to work fine now

[Dano]

Very interesting, I tried it and it did as you said, had something to do with the clear contents
Here's an updated version
Attachment 18565

I will be removing the original

I have downloaded it and it seems to work fine now

Dave, you are amazing!!! This has been on my mind for over 6 months. Thanks again for all of your help.