Hi all. I have searched the internets through and through but I think this problem exceeds just google searches, hence decided to post here. Disclaimer: I have no idea how to write a macro in Excel from scratch lol. This one that I will mention here I created through combining two found online and tweaking them a bit to somehow make it work the way I wanted - I was amazed that it actually worked. But now people think I can actually do this, and i got assigned a task that slightly blows my mind. Here it goes.
Basically, I am in need of a macro that spits out a random sample of rows into another tab. I have that for a certain %, and I also have one that you can input a specific number of rows you'd like. But now that I need is for the macro to spit out rows based on tough conditions.
I have a spreadsheet with claim information from different regions and distributors. For each region, the sample should include 6% of total claims from each distributor from that region, or at least 1 claim. So I created a table like this:
REGION DISTRIBUTOR TOTAL CLAIM COUNT SAMPLE SIZE -> 6% of total claim count, or 1.
Now I also have a table with all claim on another tab.
Task for the macro: Look at Sample Size tab, look at each individual distributor and based on the sample size assigned to that distributor, pull that many claims from the overall tab.
Here is what I have as for my regular random sample code:
Option Explicit
Option Base 1
Sub CopyRows()
Randomize
Dim LastRow As Long
Dim NbRows As Long
Dim RowList()
Dim test As Long
Dim I As Long, J As Long, K As Long
Dim RowNb As Long
Dim GoodMatch As Boolean
Sheets("Claims").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
NbRows = LastRow * 0.06
ReDim RowList(1 To NbRows)
K = 0
Do
If K = NbRows Then Exit Do
'check to see if result has already been chosen
Do
GoodMatch = True
'formula cribbed from help on rnd()...
RowNb = Int((LastRow - 2 + 1) * Rnd() + 2)
For J = 1 To K
If RowNb = RowList(J) Then
GoodMatch = False
Exit For
End If
Next J
If GoodMatch Then
K = K + 1
RowList(K) = RowNb
Rows(RowNb).Copy Destination:=Sheets("Sample").Cells(K, "A")
Exit Do
End If
Loop
Loop
End Sub
Is there any one that could possibly help me with this?
Basically, I am in need of a macro that spits out a random sample of rows into another tab. I have that for a certain %, and I also have one that you can input a specific number of rows you'd like. But now that I need is for the macro to spit out rows based on tough conditions.
I have a spreadsheet with claim information from different regions and distributors. For each region, the sample should include 6% of total claims from each distributor from that region, or at least 1 claim. So I created a table like this:
REGION DISTRIBUTOR TOTAL CLAIM COUNT SAMPLE SIZE -> 6% of total claim count, or 1.
Now I also have a table with all claim on another tab.
Task for the macro: Look at Sample Size tab, look at each individual distributor and based on the sample size assigned to that distributor, pull that many claims from the overall tab.
Here is what I have as for my regular random sample code:
Option Explicit
Option Base 1
Sub CopyRows()
Randomize
Dim LastRow As Long
Dim NbRows As Long
Dim RowList()
Dim test As Long
Dim I As Long, J As Long, K As Long
Dim RowNb As Long
Dim GoodMatch As Boolean
Sheets("Claims").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
NbRows = LastRow * 0.06
ReDim RowList(1 To NbRows)
K = 0
Do
If K = NbRows Then Exit Do
'check to see if result has already been chosen
Do
GoodMatch = True
'formula cribbed from help on rnd()...
RowNb = Int((LastRow - 2 + 1) * Rnd() + 2)
For J = 1 To K
If RowNb = RowList(J) Then
GoodMatch = False
Exit For
End If
Next J
If GoodMatch Then
K = K + 1
RowList(K) = RowNb
Rows(RowNb).Copy Destination:=Sheets("Sample").Cells(K, "A")
Exit Do
End If
Loop
Loop
End Sub
Is there any one that could possibly help me with this?