Randomly select the data in excel

[vish2025]

Hi everyone,

Could some one help on selecting data randomly as I need to select some data?
I have a column which contains data and I need to select same data daily and user can describe the the same in tables. Like for example in my case I required to select records by “H” Column
PAGE %
A1_EXC 10% of total lot
A1_INC 10% of total lot
A2_A3_INC 9% of total lot
AEs 9% of total lot
AMETH 8% of total lot
CEHx 8% of total lot
CHEM 7% of total lot

Assist on this, attached is spread of clear understanding.

Vish

[Marcol]

What Column is "total lot" in this

A1_EXC 10% of total lot

[vish2025]

What Column is "total lot" in this

Thanks for the reply and Query.
There is no such column by "Total Lot".
It's means the Parameter for taking the % of total "A1_EXC" for eg. if "A1_EXC" having 100 rows than it should take 10% of 100 i.e 10 nos.

Vish

[MarvinP]

Hi Vish,
Insert a colum to the right of column H. Put in I1 put "Rand". In I2 put "=Rand()".
Copy this formula down to all 7000+ rows. Now sort your table on column I. Pick the top 10 percent of the rows and this will be a random sample.

[vish2025]

This will be applicable if user has to select from 1 page variable but their r 60 variable and user can do it manually for all page variables.
So I required the macro which randomly select the data by page variables which will the describe by user and paste in new worksheet.

[Marcol]

What has to happen with this?

Please Login or Register  to view this content.

The last column of the above is the number of times each code appears in Sheet1 Column H, using your sample.
How do you get, for example, a random 10% sample of only one row?

What am I not understanding from your brief?

[Saarang84]

Will look into this requirement later today.

[vish2025]

That was just example basically the limited page parameter should be select from this page. Like

Please Login or Register  to view this content.

Vish

[Saarang84]

Hi Vish,

I've developed a small piece of code from the one that i use, the only thing i'm trying to fix up for you is the randomizing the selection of 10% of records as per your requirement. I'm trying to develop / finalize a generalized sampling code for choosing x% and as i PMed you earlier, its WIP. Probably, i can post the code that i wrote here, we can get help to complete our requirements.

Sarang

[Saarang84]

As discussed, you post the access database with dummy data, which can be used to query and pick the records randomly as required.

[Saarang84]

Hi Vish,

Here are the modules developed (I've used your workbook, but i've removed the column titled Protocol):
1. RandomExtraction - This module extracts x number of records input by the end-user at runtime from a worksheet.
2. RandomFilter - This is a more advance module which filters out specific pages and marks all the rows that are filtered. This needs to be tweaked a bit to suit your requirement to filter and mark x% of records.

Please feel free to let me know about any clarifications with the code

Both modules are different ways of approach, but i would vote for the second one which will be efficient since is a bit more advanced. It has a few loose ends which needs to be fixed.


Sarang

[Saarang84]

I've one query regarding Marvin's way of approach which i've used in the RandomFilter module i posted above.

I load the "=Rand()" formula under a column titled Flag, but how can the top 10 percent of the rows be picked?

Any suggestions to the code, Marvin?

Sarang

[MarvinP]

Hi,
There are at least two ways (more now that I think about it)
1. Sort by the random column top to bottom. See how many rows are in the entire column and take 10% of that number.
2. Use conditonal formatting in the unsorted Random() column. Color/format the top or bottom 10%
3. There is most likely a Large(Range, 10% or no or rows) formula and Small would work also.
4. How about picking all random numbers that have a .1 in the tenths digit?
5. This seems like it could go on all random digit day.

The idea is that Random() put a random number in each row. Use these random numbers to help you select.
On another note. If you recalculate the Random numbers will all change.

Example: Put random numbers in an entire column. Conditional Format the numbers to color the bottom 10%. Find two people you want on the same team and press Calculate until they are both CF colored. Use this as your "Random" sorting.

[Saarang84]

Hi Marvin,

Could you please explain with the code that i've posted on how to go about implementing your approach? I'm not pretty much comfortable playing around with the filtered data.

[MarvinP]

Hi Saarang84,
It looks like you added the "Flag" column head on the Source sheet correctly. BUT then you set your autofilter range from A:R instead of A:S which would include the Flag Column. After the Rand() is part of the table, sort by it. Then take the top 10% of the rows as your answers.

(You know to un remark the rand() line in your code. Right?)

I've read some of your code but it's hard to understand where you are going. I don't know what you want the final answer to look like.

I can't believe I'm reading your code and understanding the lines. The bigger problem is I don't see where you are headed. Perhaps you should start a new thread with a code review question.

[vish2025]

Hi,

Thanks for your codes and suggestion on this thread.

I really appreciate your help on this thread.

In actual I was extract the data from access and try to select random records for the same.

Is it possible to extract the records in access only and get in excel.

Let me know so I will put the new thread for the same.

Vish

[Saarang84]

No need of new thread Vish. Just try to a mini version of the access database with few hundred dummy records (limit the zipped file size to max 1 MB) so that the requirement can be solved

[vish2025]

I try to attach the min version of the access database with few hundred dummy records but zipped file size goes more than 1MB so not able to attach the same.
But i forwarded the same to your personal email-id with details requirment.
Kindly review and help on the same as this is hitch me from last 2 weeks.
Vish

[Saarang84]

Hey Vish,

Had been having problems with my desktop since my last favourable post. Had fixed it over last weekend. Had been searching in and out for some old querying code which i did more than 2 years back. Will definitely post it shortly. Probably it may help your requirement.

Sarang

[Saarang84]

Hey Vish,

Can you briefly explain as to how you want to extract data from the access database? I already have an interface in excel using which i can extract records using queries / joins into different excel sheets.

[vish2025]

Hi Saarang,

Thru sql query I will extract the data from access to query .
for eg.

Please Login or Register  to view this content.

Let me know if you required any more details

[Saarang84]

Hi Vish,

I got the code which i was looking for from an archived folder in the network. I'll post it over the weekend, modified as per your requirement.

Sarang

[Saarang84]

Hi Vish,

Under the tools menu in the Visual Basic Editor in Excel (invoked by Alt+F11) choose References option and enable (tick the checkbox) the following references (files found in the \Program Files\Common Files\Microsoft Shared\OFFICE** folder):
Note: ** will change based on the Office version used.

1. Microsoft Office 12.0 Object Library (found in Common Files\Microsoft Shared\OFFICE**\mso.dll)
2. Microsoft ActiveX Data Objects 2.7 Library (found Common Files\System\ado\msado27.tlb)

Then, include the below code in a standard module

Please Login or Register  to view this content.

Store and execute the query inorder to choose records from the database table at the click of a button (cmdExtract button code).

In case of any queries, do let me know.

Sarang