Random Selection of Visible Rows after Filtering

Hi,

I'm posting this thread as part of a random sampling requirement for a process. I've posted in this forum earlier for this and the related thread link are as below:

Filter & Flag as Sample
Randomly Select Data in Excel

As requested by Marvin, i'm posting this thread for a code review, probably, i can explain my code line by line if required. I've posted my code and sample workbooks in the above threads. The main idea is to randomly select rows of data for each user one after another. The records of each user are filtered out and from the filtered set of rows, i need to choose a certain number (say x% of total visible rows) of rows as samples and mark them.

I came across a function for sampling random records (from a worksheet) and i some help to tweak it. I've posted it in this forum in another thread, (which i use for another macro, thread link below) but the only requirement for this function to work in the above case is that the filtered range needs to be continuous.

How to filter & sort columns to get data in a continuous range

If as per Marvin's suggestion, sorting the column of random values will help to get a continuous range of data, then how can that be done? If it doesn't work, then, how can the filtered range be made continuous?

Sarang

Hi Saarnag84,

I think the Rand() column method SORTS the table by the Rand column and then takes the top x% of the sorted rows. No filtering is needed. I wonder if you are filtering by the top x% and then trying to grab the values by row numbers? This may be why you are having problems.

Hi Marvin,

I'll post back tomorrow (probably 8 or 9 hours from now) with a clear explanation of my code line-by-line. Probably, that'll help you understand about my requirement clearly.

Sarang

Whos' thread are we reviewing here Saarang84 or vish2025

Has there been a bit of hijacking going on here? vish2025 has hardly managed a word in edgewise here

http://www.excelforum.com/excel-prog...-in-excel.html

This thread is confusing enough.

Hey Marcol,
I've been wondering the same thing. That is why I suggest Saarang84 start his own thread. He found the vish2025 question about selecting random stuff and found some code and can't make it work. I had done a simple suggestion of putting in a Rand() column and sorting it by that column. I directed him to start his own thread. The new question is how to make his code do what he wants. The code looks good but I don't think he knows where he is going.

Hi MarvinP

No critisism of you, you have done well, and thank you.

I just feel that vish2025 needs a little more help, and indeed a little more effort on his/her behalf in explaining her/his needs.

Hi Marcol & Marvin,

Just to clarify, this is my thread (first link is mine and the other one is Vish's which are posted above)
Vish came across my thread and asked me for some help. I spoke with him and even tried helping him with my code which Marvin also had looked into and provided some inputs.

I just needed some help with my way of approach (which i thought Marvin can do and i need some help to implement it in my code) and i started this as a new thread with all relevant links to my earlier post and also linked Vish's thread here.


Sarang

Both mine and Vish's requirement are the same.
The requirement is to filter and sample a certain % of the number of visible records output by the filter.

I've broken the code into parts and i've tried to explain what i'm trying to accomplish with it:

Please Login or Register  to view this content.

The above part declares the variables required and i start with searching a column named DATA1 and include a new column at the end of the sheet after the last column and name it FLAG. Then i load the RAND() formula using FormulaR1C1.

Please Login or Register  to view this content.

In this part, the execution continues and in a for loop i set the autofilter field for that column DATA1 (which is the first column) and i execute it for all the 10 unique values in that column (limits of the for loop).

Please Login or Register  to view this content.

In the above part, i calculate the number of rows to be chosen as samples and store it in the variable called randRow. vCellCount contains the number of visible rows output by the filter set during each iteration of the for loop.

Please Login or Register  to view this content.

In this last part, using the .Resize line, i want to randomly mark the number of rows stored in randRow from the total rows available (stored in vCellCount). This line currently marks all the visible rows and that needs to be fixed

Hope this explanation suffices...

Sarang

Any inputs / suggestion Leith, Jerry?

Hi Saarang84,
Does your code compile correctly? Have you set breakpoints and stepped through the code? These are the steps programmers would be working on to solve their problems. We don't know what your final answer would look like or what went wrong in the process (in code) above. I think you can figure it out on your own by breakpoints and stepping. This is what I do (way too frequently) to find code problems.
Some links to help are at:
http://www.cpearson.com/excel/Debug.htm
http://www.techbookreport.com/tutori...el_vba_01.html Both Pages.
http://office.microsoft.com/en-us/he...001042819.aspx

You are ready to tackle your code with the above tools. I think they will help you figure it out.

Thanks for the links Marvin, my code compiles and runs correctly.

In my code, i'm trying to mark a certain number of rows randomly from the set of rows output by the filter. The code line below marks all the rows, but i need not have all the rows marked.

Please Login or Register  to view this content.

For example, if there are 100 visible rows output by the filter, then i need to mark a certain x% of these visible rows. Say if x is 10%, then i need to mark only 10 rows out of these 100 visible rows randomly. But the above code, the line .Resize marks all the visible rows. How can this line be tweaked / fixed to mark only the required number of rows randomly?? Can you help??

Sarang

The method of putting in Rand() was to SORT not FILTER on that column. You keep filtering which was not why I put in the Rand().

Example: Say you have 1500 rows. You SORT them suckers by Rand(). The smallest Rand() come to the top of the list. Then you simply look at the top 10% of the rows. Then rows 2 to 150 are the ones you select.

I'm sure there are other ways to do this problem but the reason for putting in Rand was to SORT and then simply take the top 10%.

I call this problem in programming "Forest from the Trees". I believe you are looking so closely at the code you're not thinking how the code resolves the whole problem. Step back and see why Sorting instead of Filtering might be a different solution.

I have this problem frequently when I can't find the darn Object that I need to use to do something. Many times I end up never finding the Object and resolve the problem with things I do have the vocabulary for. I blame MY Excel Object Library Vocabulary DEFICENCY for this problem. I also find new VBA functions (like Split() and Join()) that weren't around when I learned to code in Basic so many years (lifetimes) ago. They change the way I attack new problems - different ways based on where I'm going (trying to solve).

The short answer: Sort, don't Filter. Then take the top 10% of the SORTED rows (by row number).

Hi Marvin,

Thanks for that last line in the above post. Yes, you are right. I was indeed trying to filter on the Rand() column and didn't think of sorting it.

Now my question is how to select the top 10% of rows alone and mark them?? Do you mean that alongside the Flag column, should the row numbers be filled in using .FormulaR1C1 "=ROW()-1"? Then how can these rows alone be selected and marked?

After sorting, find the number of total rows, using End(xlUP). Use this number divided by 10 for the number of rows to select. Then do a for next loop from 2 to this number as your selections. There are a lot of different ways to accomplish selecting 10% of the rows. This is just one of them.

Hi Marvin,

I'm sorry to bother you so much... If you can add a few lines of VBA of what you had described above in the appropriate place in the whole module (in the workbook attached), it will be helpful for me to probably enhance it and fulfill my requirement.


Sarang

Hi Marvin,

I did exactly what you said in the above post and it works like a Gem. I very much really appreciate this code that you've given me...

It was a dummy set of data that i posted with the code that i had built earlier. I've herewith attached a sample workbook which i originally use where i'm trying to incorporate the code.

The thing i want to accomplish is to obtain a random set of records by filtering for each name in the column titled LAST_UPDATE_NAME. I need randomly chosen records for each user as listed in the Summary sheet.

Your recorded code picks the top 10% of rows in general, but my requirement is to choose the top 10% of visible rows after filter is applied to a particular column. Can you please advise / explain on how the code works and suggest modifications required if any?

Sarang

Hi Sarang,

It is time you buy a book and start learning to code VBA yourself. I did the random sort to show you how it is done in general, so you could use it as an example and expand on the code. There are a lot of great books on VBA programming. The help system behind the VBA editor is also available to understand what the words mean.

There is nothing like a good example to motivate you to learn on your own. Soon you'll be giving coding answers instead of asking for them.

Thanks Marvin.. Will try to apply it to the original workbook and work out a solution. I understand the code a bit, probably will get back to you in case i need any clarifications. Thanks once again.