Advanced Excel Techniques Excel VBA and Macros Productivity and Automation

RANDOM Sampling Excel Tool

Random Sampling Excel Tool

When there are multiple transactions, and activities processed by large firms, it gets difficult for them to check the accuracy and quality of those data sets because it is almost impossible to monitor each transaction/activity when it comes to large data sets. Hence Data analyst uses the method of Random Sampling and they pick a few data samples as per their policy and perform the audits for them.

This helps the business to know the direction and gives the inferences about the population/data sets however Random sampling should be done by automated software or it should be logical as random sampling can be biased because of human behavior.

Excel VBA Master Tool Kit

So Excelsirji is going to help you with this free automated utility which is easy to download on any Windows-based system

What is Audit Sampling Tool?

Random Row Selector is an MS Excel based tool which can be used to pick random or stratified samples from a set of records available in the Excel.

The tool support following features:

It supports Random and Stratified Sampling
The tool is fully dynamic, it can support any data format in Excel
The codes are written to provided fast results within minimum time
Samples are marked and filtered for easy identification

What is Excel Audit Sampling Tool?

Random sampling is a method to pick certain number of records from a population of records randomly. For example, a teacher picks 4 students randomly from a class of 20 students.

What is Stratified Sampling?

Stratified sampling is a method to pick certain number of records from each population sub-group randomly. You must be able to categorize the population in one or more stratification or classification. For example, a teacher picks 2 girls and 2 boys randomly from a class of 20 students. Here gender is the stratification method.

Why Sampling is Important?

In the modern business, there are millions of transactions processed every day. To ensure the accuracy of the transactions, each business picks few transactions and audit them as per defined checklist or audit parameters. Picking the right samples is also a big task, you must follow basic rules to pick the samples:

The samples must not be biased. For example, picking samples of a specific agent or country and not including other agents or countries in the samples
Samples should be picked randomly and not in a specific order. For example, picking samples of a specific date instead of picking from entire month
Samples should be picked with a defined rule so that it represents entire population

How Random Rows Selector Tool helps you?

This tool helps to mitigate all the issue which may occur if you manually pick the samples. And the best part is, it is truly random. Just try to use the tool and we are sure that you would love it for its simplicity and high performance.

How to use this tool:

Step 1: Download the latest version as below

Note Free version comes with few limitations. Please buy business version of Random Rows Selector Tool to get all features and access to backend VBA code.

Step 2: Unzip the file and open

Step 3: Paste your data from which you want to pick the samples in the ‘Home’ sheet

Note:

Please keep column A and B empty, these columns are used by the tool for calculations
Column C should not have any empty records
You are free to rename or add new columns in the sheet

Step 4: Click on ‘Open Tool’ button

Step 5: Random Selection: If you want to pick random records from the data, enter the number of records to you want to pick and click on ‘Pick Samples’ button

Step 6: Tool will pick the samples from the data and filter them for you

Note: The samples will be marked in green color to easy identification

Step 7: Stratified Selection: If you want to pick random records from the data but wants to ensure that at least few records are picked from each category (such as Country). Enter the number of records to you want to pick from each category and select the category name. Once done, click on ‘Pick Samples’ button

Step 8: Tool will pick the samples from the data and filter them for you

Note: The samples will be marked in green color to easy identification. In case, there are not enough records available to meet the minimum sample required then tool will pick the available records.

Step 9: Stay tuned with Excelsirji.com for more free tools and templates

Recommended Articles

VBA Tool to Merge Excel Files from a Folder

Productivity and Automation | Advanced Excel Techniques | Excel VBA and Macros

VBA Tool to Merge Excel Files from a Folder

Byexcelsirji

Merge Excel Files From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free…

VBA Code to Add Items in ListBox Control in Userform

Excel VBA and Macros

VBA Code to Add Items in ListBox Control in Userform

Byexcelsirji

VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…

File Manager Tool

Advanced Excel Techniques

File Manager Tool

Byexcelsirji

File Manager tool is an Excel based tool which helps you to delete or move unwanted files from your system. It requires a source and destination folder (in case you want to move files). First it lists all the files available in the folder or sub-folders then you can select the action to be taken for each file such as Move or Delete. With a click of button, tool will take all necessary actions.

Duplicate Data Identifier

Advanced Excel Techniques

Duplicate Data Identifier Tool

Byexcelsirji

Duplicate Data Identifier is an MS Access based tool which helps to identify duplicates from any Excel based data. The tool supports up to 10 conditions and 25 types of matching conditions to find the exact duplicate. You can also define formatting conditions to first format the data before checking for duplicates.

VBA Code to Count Cells by Color

Advanced Excel Techniques | Excel VBA and Macros | Productivity and Automation

VBA Code to Count Cells by Color

Byexcelsirji

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

VBA Code to Get User Name

Productivity and Automation | Excel VBA and Macros

VBA Code to Get User Name

Byexcelsirji

This Excel VBA Code helps to Get User Name. Here is an example environ(username) or Application.username.This macro gets the username from active directory.

4 Comments

Ankitsharma says:

at

Hi,
This tool is very useful and awesome.
Can you please provide VBA Password for better understanding.
Thank you

Reply
1. excelsirji says:
  
  at
  
  Hello Ankit,
  
  Thanks for contacting ExcelSirJi team.
  
  As you know that we at ExcelSirJi work hard to help our subscribers and visitors to make full use of the free codes and templates published by our team.
  
  As part of our objective, you are free to use these templates. However to access the code, you may refer the below link to get unprotected version
  
  Please visit Random Sampling Tool to know more about the unprotected version.
  
  Regards,
  Your Excel Mate
  
  Reply
Archana says:

at

Hi,
Just wanted to know that the version which can be downloaded from site is a protected or unprotected version. Second can we get the VBA codes for this version.
Thanks. Archana

Reply
1. excelsirji says:
  
  at
  
  Thanks Archana for showing interest in the tool.
  
  Free version is password protected and if you want to access VBA code, you need to purchase the premium version cost $6.99 for personal use and corporate use, it is $149.99. You can contact us on our email ID [email protected]. Currently purchase mode from website is off due to some technical development and that will be available soon.
  
  Let me know should you have any questions. Have a nice day ahead.
  
  Reply

Leave a Reply Cancel reply

Excel VBA Master Tool Kit