Excel 2010 VBA - Pulling Random Rows of Data Based Upon Criteria 1

[JasonEnsor]

Hi Guys,

I'm looking for inspiration/help on a 'quick' project i have been asked to look at and was hoping to get some advice/help.

The Task: Each month a report is downloaded from our PeopleSoft Database in to an Excel report. The report holds information about student fees. Each report has the same headings
ID Item Type Sum Line Amt Acct Term Posted Descr Waiver Code

From this report a random number of students need to be selected based upon the value in Descr.

So if i had 10 Students whose Descr was Waiver 1000, and 5 Students whose Descr was Waiver 500. I might decide i want to see 10% of the students with Waiver 1000 and 2% with Waiver 500.

The % should be able to be set at runtime.

My Thoughts: So my initial thought is to grab the Descr column and populate a userform with the unique values, then for each value have an area for the user to populate the number of results they would like to recieve. I was thinking if i used a listview i could just populate the first column with the Descr and then in the second column show the overall % of the Descr item (i.e So if we had 100 Descr's and a particular one showed up only once it would be 1%) then on the 3rd column the user could input the number of results they wish to recieve. However i think ideally it would be good to be able to enter the number of results you want to recieve and then based upon the percentage numbers for each group for the vba to calculate how many that equates to for each group.

I would then pass the random rows selected to a temporary worksheet for the user to view/print/save etc..

Code I'm thinking about writing:

Creating the userform and the listbox shouldn't be an issue, my main problem i think is going to be getting the random lines of data based upon the Descr. I also think calculating the Percentage might be an issue. I'd assume i could count the rows of data in excel, then count each occurance of the Descr to calculate the Percentage. How would be best to do this?

Any Thoughts/Ideas would be appreciated. I'm not expecting to be given all the code i need to write, just a few pointers really.

Many Thanks As Always

J.

Regards

J.

 

[Deniall]

(Straight off the top of my head.)

If you want to select at random a given percentage of students in a description category, you will need to change the odds as you move down your list.[ ] Suppose you have a total of K students in the category, and you wish to select L students (where L has been previously calculated as being the integer nearest to X% of K).

1.[ ] Set I=K and J=L.
2.[ ] Generate a random floating point number in the range zero to one.[ ] I think VBA contains a function that does this for you.
3.[ ] Set a Yes/No flag for the first student based on whether this random number is less than or greater than the floating point number J/I.
4.[ ] Set I=I-1.
5.[ ] If the processed student was a Yes, set J=J-1.[ ] Otherwise leave J unchanged.
6.[ ] Repeat steps 2 through 5 for all remaining students in the category.

This approach should be able to be implemented using just your original table of data, with the VBA operation creating one additional Y/N column for every row in the table.

I think that this approach will guarantee you achieve your percentages.[ ] Also I am fairly sure that after you allow for the probability-chain each student has an equal chance of being selected, but some statistician out there in Tek-Tips-land might be able to confirm this one way or the other.

 

[Deniall]

I managed to find a used envelope in my rubbish tin.[ ] On the back of it I have satisfied myself that my approach ensures that all students in the same category have the same probability of being selected.[ ] And the envelope was only 90x145.

 

[JasonEnsor]

Hi Deniall,

Thank you so much, it would seem i have this working almost perfectly now. The only issue i have found is when i have a category with only one student, it is not picking that student to add to my list. I can probably get around this by doing a check on the number of students in the group and the number of students required for the list. After staring at this all day though i think i need a break.

Thank you again for your help with the logic to solve this issue, i probably wouldn't have gotten very far without it.

J.

Regards

J.

 

[Deniall]

It occurred to me later that there is another completely different approach that could be taken.[ ] Sort your table into random order.[ ] Scan down the randomised table and select the first L students.[ ] Sort the table back into its original order.