Implement Advanced Data Sampling Techniques with Excel VBA

Advanced Data Sampling Techniques in Excel VBA

When working with large datasets in Excel, advanced sampling techniques can help you select representative subsets of data. These subsets can be used for analysis, testing, or decision-making without overwhelming the system with the entire dataset. We’ll focus on some of the most common techniques like Random Sampling, Stratified Sampling, and Systematic Sampling.

Key Sampling Techniques:

1. Random Sampling
   • Every data point in the dataset has an equal probability of being selected.
2. Stratified Sampling
   • The dataset is divided into distinct subgroups or strata, and a random sample is taken from each group.
3. Systematic Sampling
   • The sample is selected by choosing every nth data point from a dataset after randomly selecting a starting point.

We will write VBA code for each of these techniques.

Step-by-Step VBA Code Implementation for Advanced Sampling Techniques

1. Random Sampling

Random sampling involves randomly selecting a number of data points from a larger dataset.

Concept:

• If you want to randomly sample n rows from a dataset in Excel, you could generate random numbers and use them as criteria to choose which rows to sample.

Sub RandomSampling()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim sampleSize As Integer
    Dim i As Integer
    Dim randomRow As Integer
    Dim sampledData As Range
    Dim sampledRows As Collection
    Dim newRow As Long   
    ' Set the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = ws.Range("A2:B100") ' Example data range (from A2 to B100)   
    ' Number of samples you want
    sampleSize = 10   
    ' Collection to store sampled rows
    Set sampledRows = New Collection   
    ' Loop to get the required number of samples
    For i = 1 To sampleSize
        ' Generate a random row number between 2 and the last row in the data range
        randomRow = Int((dataRange.Rows.Count - 1 + 1) * Rnd + 2)       
        ' Check if the row has already been sampled
        On Error Resume Next
        sampledRows.Add randomRow, CStr(randomRow) ' Add row to collection
        On Error GoTo 0
    Next i   
    ' Copy the sampled data to a new range
    newRow = 1
    For Each randomRow In sampledRows
        dataRange.Rows(randomRow).Copy Destination:=ws.Cells(newRow, 5) ' Paste to column E
        newRow = newRow + 1
    Next randomRow
End Sub

Explanation:

• Random Number Generation: The line randomRow = Int((dataRange.Rows.Count – 1 + 1) * Rnd + 2) generates a random row number.
• Sampling: A Collection object is used to store the unique rows that are selected.
• Result: The selected rows are copied and pasted into a new column (column E in this case).

2. Stratified Sampling

Stratified sampling divides the data into distinct subgroups or strata, and random samples are taken from each subgroup.

Concept:

• We divide the data into different categories or groups (strata), and then we sample randomly within each group.

Sub StratifiedSampling()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim uniqueGroups As Collection
    Dim group As Variant
    Dim groupData As Range
    Dim groupSampleSize As Integer
    Dim sampledData As Range
    Dim randomRow As Integer
    Dim newRow As Long   
    ' Set the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = ws.Range("A2:C100") ' Example data range (A2 to C100 with Group in column C)   
    ' Get unique groups from column C (assumes group is in column C)
    Set uniqueGroups = New Collection
    On Error Resume Next
    For Each cell In dataRange.Columns(3).Cells
        If cell.Row > 1 Then
            uniqueGroups.Add cell.Value, CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0   
    ' Loop through each unique group and sample
    newRow = 1
    For Each group In uniqueGroups
        ' Filter data for the current group
        Set groupData = dataRange.Columns(3).Find(group).EntireRow       
        ' Define sample size (for simplicity, we take 2 samples from each group)
        groupSampleSize = 2       
        ' Randomly sample from this group
        For i = 1 To groupSampleSize
            randomRow = Int((groupData.Rows.Count - 1 + 1) * Rnd + 2) ' Random row within group
            groupData.Rows(randomRow).Copy Destination:=ws.Cells(newRow, 5)
            newRow = newRow + 1
        Next i
    Next group
End Sub

Explanation:

• Grouping: First, we extract unique groups from the dataset (assumed to be in column C).
• Sampling: We then loop through each unique group and perform random sampling within each subgroup.
• Result: The stratified samples are copied to a new location.

3. Systematic Sampling

Systematic sampling involves selecting every nth row after randomly selecting a starting point.

Concept:

• Choose a random starting point, then select every nth row from the dataset.

Sub SystematicSampling()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim sampleInterval As Integer
    Dim randomStart As Integer
    Dim i As Integer
    Dim sampledData As Range
    Dim newRow As Long   
    ' Set the worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set dataRange = ws.Range("A2:B100") ' Example data range (A2 to B100)   
    ' Define the sample interval (every 5th row)
    sampleInterval = 5   
    ' Randomly select a starting row between 2 and sampleInterval
    randomStart = Int((sampleInterval - 1 + 1) * Rnd + 2)   
    ' Loop through the data with the selected interval
    newRow = 1
    For i = randomStart To dataRange.Rows.Count Step sampleInterval
        dataRange.Rows(i).Copy Destination:=ws.Cells(newRow, 5) ' Paste to column E
        newRow = newRow + 1
    Next i
End Sub

Explanation:

• Interval Sampling: The sample interval is set by the variable sampleInterval, and the starting point is randomly selected.
• Loop: The loop selects every sampleInterval-th row starting from the random position.
• Result: The systematically sampled data is copied to a new location.

Conclusion:

In this guide, we demonstrated how to implement Random Sampling, Stratified Sampling, and Systematic Sampling using Excel VBA. These advanced sampling techniques are helpful for extracting subsets of data from large datasets for analysis. Each technique can be adjusted by modifying the parameters (e.g., sample size or interval), and the code can be further customized for specific requirements.

By using VBA, you can automate the data sampling process, saving time and reducing the potential for human error in handling large datasets.