Sampling after Auto-filtering

[rangudu_2008]

Hi,

The other day i was browsing through one of the many excel forums on web, (it was on a forum called Xtremevbtalk) i came across a code that uses auto-filtering.

http://www.xtremevbtalk.com/showthread.php?t=305425&highlight=random+sample+filtered+rows (http://www.xtremevbtalk.com/showthread.php?t=305425&highlight=random+sample+filtered+rows)


Can someone explain me more clearly (step-by-step using layman terms) as to how to go about using this autofiltering / copying to accomplish the following?


I've a big chunk of data (which is used to choose samples) which is filtered frequently and is used up in parts to determine the samples for various users. I need to export data from this big chunk to new sheet(s) by creating (sort of) a recordset (like in a database) and this must happen every time when data is being exported.

When I say I want to export data to new sheet(s), I only need to determine the filtered rows and not actually exporting (copying) the filtered data (to a new sheet) at that point of time (when the code is in execution) – Only the identified row numbers needs to be stored temporarily (in an array or a defined data structure).

Since this sampling needs to be done recursively (for various users), I thought of setting some sort of flagging points (to identify the rows that have met the pre-defined sampling conditions) and also need to randomly sample out these identified rows (I've coded a separate module to randomly pick out rows) taking one user at a time. Probably these flags can be cleared out every time and reset whenever required.

This way i can have a control of the data being copied to many sheets from the master sheet (and avoid duplication) and also can consolidate the samples chosen for various users to a single sheet.

Can someone help me??

Ranga

[MWE]

A few thoughts to get you going:

1) given a range that is to be filtered, for example, the range object "OrigRange", you use AutoFilter to filter one or two columns based on some criteria. The AutoFilter method only allows two cols/criteria but you can play a little with booleans in the criteria:
OrigRange.AutoFilter Field:=ColNum, Criteria1:=SearchText 2) you then "capture" whatever is left and copy it somewhere. For example, continuing with 1), you capture the visible rows left in a range variable (FilteredRng)
Set FilteredRng = OrigRange.SpecialCells(xlCellTypeVisible).EntireRow 3) and then copy it to some target location, e.g., another worksheet object named "xlsheet"
FilteredRng.Copy xlsheet.Range("A2") 4) if appropriate, you toggle off the autofilter operation to reveal all of the original content of OrigRange:
OrigRange.AutoFilter You can continue this operation many times sequentially refiltering whatever was left from the previous filtering operation

Does this help?

[rangudu_2008]

Hi Mr. MWE,

Thanks for the clear explanation given by you.

I had developed a few modules for automating a manual process which requires sampling to be done at random – Out of these modules, i need to combine 2 modules and to make them work hand in hand. One of the modules is called "Filter_N_Transfer" and another is called "Random Sampler".

I've tried to take care of what you've explained above in the 1st module (F_N_T)
With the other module "RandomSampler" i proceed a step further going on to pick a few sample rows. I use the RAND() function for this purpose, and it generates random numbers (into a column) which is randomly picked out and then the whole row is copied to another sheet. (I actually get the no. of rows to be randomly picked out to from the end-user)The only thing i'm trying to accomplish (to make these 2 modules to work together) is explained below:

After applying autofilter and filtering out a few records, how can i randomly pick out and copy those chosen rows to a new worksheet?

If the filtered rows are copied to some target location as you say, at the end, i finally need to consolidate all those copied rows to a single sheet.

As mentioned before, I need to do this for various users and at the end i'll only have a few rows of data from the big chunk which i start with.

How can this be done?? :help :help

P.S. I've attached the VBA codes of the modules that i've developed which are mentioned above.

The F_N_T module has a sample chunk of the data which i filter and sample. There is column called LAST_UPDATE_NAME (col AQ) which has the names of the users for whom i need to choose samples based on 2 other columns NOTES (col R) and KYC_ML_SCORE (col AO)

Can you help me combine these modules together?

Ranga

[MWE]

Hi Mr. MWE,

Thanks for the clear explanation given by you.

I had developed a few modules for automating a manual process which requires sampling to be done at random – Out of these modules, i need to combine 2 modules and to make them work hand in hand. One of the modules is called "Filter_N_Transfer" and another is called "Random Sampler".
I've tried to take care of what you've explained above in the 1st module (F_N_T)
With the other module "RandomSampler" i proceed a step further going on to pick a few sample rows. I use the RAND() function for this purpose, and it generates random numbers (into a column) which is randomly picked out and then the whole row is copied to another sheet. (I actually get the no. of rows to be randomly picked out to from the end-user)The only thing i'm trying to accomplish (to make these 2 modules to work together) is explained below:

After applying autofilter and filtering out a few records, how can i randomly pick out and copy those chosen rows to a new worksheet?

I will look at your spreadsheet and code later today, but, in the meantime ...

What does "randomly" mean in " ... randomly pick out and .." above

If I understand you correctly, you wish to identify the records (rows) that were filtered out and copy them to a new worksheet. This is the opposite of copying the rows that are left after the filter operation. It would be nice if you could select xlCellTypeInvisible, but that is not an option. Probably the simpliest way is to sequence down the original range checking for row visibility. Hidden rows are then added to a range object using the Union method and when done the new range object is copied or whatever.


If the filtered rows are copied to some target location as you say, at the end, i finally need to consolidate all those copied rows to a single sheet.

Not sure I understand the issue. If you filter "A" and copy the filtered results to "B" and then filter "B" and copy the results to "C", ... you have the consolidated final results when you are done.

If, you mean that the results of each set of filtering operations results in some set of targets and you wish to consolidate all those targets to a single sheet, then you simply copy each target to the final sheet either as the targets are created or "at the end"

As mentioned before, I need to do this for various users and at the end i'll only have a few rows of data from the big chunk which i start with.

How can this be done?? :help :help

P.S. I've attached the VBA codes of the modules that i've developed which are mentioned above.

The F_N_T module has a sample chunk of the data which i filter and sample. There is column called LAST_UPDATE_NAME (col AQ) which has the names of the users for whom i need to choose samples based on 2 other columns NOTES (col R) and KYC_ML_SCORE (col AO)

Can you help me combine these modules together?

Ranga

[MWE]

Hi Mr. MWE,

Thanks for the clear explanation given by you.

I had developed a few modules for automating a manual process which requires sampling to be done at random – Out of these modules, i need to combine 2 modules and to make them work hand in hand. One of the modules is called "Filter_N_Transfer" and another is called "Random Sampler".
I've tried to take care of what you've explained above in the 1st module (F_N_T)
With the other module "RandomSampler" i proceed a step further going on to pick a few sample rows. I use the RAND() function for this purpose, and it generates random numbers (into a column) which is randomly picked out and then the whole row is copied to another sheet. (I actually get the no. of rows to be randomly picked out to from the end-user)The only thing i'm trying to accomplish (to make these 2 modules to work together) is explained below:

After applying autofilter and filtering out a few records, how can i randomly pick out and copy those chosen rows to a new worksheet?

If the filtered rows are copied to some target location as you say, at the end, i finally need to consolidate all those copied rows to a single sheet.

As mentioned before, I need to do this for various users and at the end i'll only have a few rows of data from the big chunk which i start with.

How can this be done?? :help :help

P.S. I've attached the VBA codes of the modules that i've developed which are mentioned above.

The F_N_T module has a sample chunk of the data which i filter and sample. There is column called LAST_UPDATE_NAME (col AQ) which has the names of the users for whom i need to choose samples based on 2 other columns NOTES (col R) and KYC_ML_SCORE (col AO)

Can you help me combine these modules together?

RangaI have looked at the files you attached and have a number of questions.
Filter & Transfer.xls
the "Filter_N_Transfer" proc did not run. I looked at the code before enabling macros (just to be safe) and noticed that you did not have an Option Explicit statement in the module. I added that and did a test compile. There were several compiler errors that fixed and, in doing so, cleaned up the code a little:
added Option Explicit (this is what caused several compiler errors)
alphabetized declared variables
added declarations to variables previously in limbo, e.g., c1, c2, op, rw
set all objects to Nothing at end
changed “Next” to “Next f” for clarity
indented consistentlyAlthough it now compiles without error, it does not execute. There is an execution problem with the statement
For Each f In ActiveSheet.AutoFilter.Filters I am not sure why this statement fails. No filter objects have been created for the AutoFilter. Or if they have, I could not find that code. But that should mean that the loop immediately drops out. I inserted a debug statement to display the value of Activesheet.AutoFilter.Filters.Count (which I expected to be zero) and it failed with the same error.

How the filters being defined? How are col numbers defined? How are criteria defined? Where are the destination targets defined? So, please look at the attached and help me understand what you are doing.

I went back and reread your original post. Honestly, I do not understand what you are trying to do. Help me understanding what you mean by "sampling", the implication of multiple users.

[rangudu_2008]

Please read through the attached document which would give you a more clear picture of my requirement.

I've added the code modules that i've developed and i need to develop a master module to make all the modules work together.

To answer to your query, read on...
The whole code works only when the sheet contains filters which are set by the end user manually.

The statement ActiveSheet.AutoFilter.Filters checks for any filters in a sheet (filters maybe upto any level, i mean any no. of columns may have filters) and loops through to get all data that is filtered and finally the towards the end of the module it filters the whole data into a new sheet which is automatically renamed as "Not SetNot Set"

As for my requirement, i need to set filters automatically (as per the conditions) and choose samples from these filtered rows.

Please help / guide me to accomplish this.

Ranga

[rangudu_2008]

Sorry missed the attachment in my previous post.

Ranga

[rangudu_2008]

Hi Aaron,

Your thoughts / inputs / suggestions / ideas on the above requirements would be highly appreciated.

Ranga

[MWE]

Hi Aaron,

Your thoughts / inputs / suggestions / ideas on the above requirements would be highly appreciated.

RangaI looked through the word doc you provided. The scope of this application is larger than anticipated and will likely become one of those long term tasks that consume far more time than I have for this forum right now. Sorry, but I can be of no further assistance right now.

[rangudu_2008]

Hello Mr. MWE,

Could you please guide / help me with combining the two modules Filter_N_Transfer & Random Sampler?

I need your help only to combine these 2 modules, I can take care of the rest. I've been browsing for days and days across various online forums and its been more than a month since i started looking for a solution. My efforts have beared no fruit until i met you. I still feel that if we could work in tandem, probably the solution is not far away.

As said before, i only need a code module which can accomplish the task of sampling where i need to combine these modules (maybe call them alternatively or do something sort of). I just need a basic code which i can (explore, fine tune and add more code) use to set filters automatically (based on user names) and choose samples from the filtered records for these users.

Can you kindly help me out?

Ranga