Sample Data

[maheshmaxi]

Hi,

I want to take sample data from a huge data for audit purpose. The data has columns "User" and "Category", Unique Users - 45 and Unique Category - 27 (number of unique users and category may slightly vary day by day). I need to take 50 sample records out of 5000+ data. I should cover all Users and Category, but shouldn't exceed 50 records for sample (no problem if user and/or category is repeated withing 50 records).

Can someone help me on this?

 

[Logit]

.

Option Explicit


Sub Random20()


Randomize 'Initialize Random number seed
Dim MyRows() As Long ' Declare dynamic array.
Dim numRows, percRows, nxtRow, nxtRnd, chkRnd, copyRow As Long
'Determine Number of Rows in Sheet1 Column A
numRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Get 20% of that number
percRows = 50                                           '<<---------------   Number of random rows to be copied
'Allocate elements in Array
ReDim MyRows(percRows)


'Create Random numbers and fill array
    For nxtRow = 1 To percRows
getNew:
        'Generate Random number
        nxtRnd = Int((numRows) * Rnd + 1)
        'Loop through array, checking for Duplicates
            For chkRnd = 1 To nxtRow
                'Get new number if Duplicate is found
                If MyRows(chkRnd) = nxtRnd Then GoTo getNew
            Next
        'Add element if Random number is unique
        MyRows(nxtRow) = nxtRnd
    Next
    
'Loop through Array, copying rows to Sheet2
    For copyRow = 1 To percRows
        Sheets(1).Rows(MyRows(copyRow)).EntireRow.Copy _
        Destination:=Sheets(2).Cells(copyRow, 1)            '<<---------------   Change sheet to copy to here
    Next
    
End Sub

Download link : https://www.amazon.com/clouddrive/share/dNUgW3bQzq5eoFqYehkJzrL9Izx2qZV4C84rhZ2Borw