Auto picking and pasting random samples from one sheet to another sheet.

[ebineg]

Hey guys,

I'm trying to pick random samples from the sheet which contains huge data ( many people data ).
I'm new to <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> so any help would be greatly appreciated.

Explanation:
i have a sheet which contains huge data of many people.which looks like below:

[TABLE="width: 557"]
<colgroup><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]username[/TD]
[TD]work[/TD]
[TD]number [/TD]
[TD]link[/TD]
[TD]worked on[/TD]
[TD]duration[/TD]
[TD]find[/TD]
[TD]no find[/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD]23[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]45[/TD]
[TD]76[/TD]
[TD]76[/TD]
[TD]7665[/TD]
[/TR]
[TR]
[TD]yyyy[/TD]
[TD]24[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]46[/TD]
[TD]77[/TD]
[TD]77[/TD]
[TD]7666[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]25[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]47[/TD]
[TD]78[/TD]
[TD]78[/TD]
[TD]7667[/TD]
[/TR]
[TR]
[TD]yyyy[/TD]
[TD]34[/TD]
[TD]4[/TD]
[TD]9[/TD]
[TD]48[/TD]
[TD]79[/TD]
[TD]79[/TD]
[TD]7668[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]56[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]49[/TD]
[TD]80[/TD]
[TD]80[/TD]
[TD]7669[/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD]78[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]50[/TD]
[TD]81[/TD]
[TD]81[/TD]
[TD]7670[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]243[/TD]
[TD]7[/TD]
[TD]12[/TD]
[TD]51[/TD]
[TD]82[/TD]
[TD]82[/TD]
[TD]7671[/TD]
[/TR]
[TR]
[TD]yyyy[/TD]
[TD]123[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]52[/TD]
[TD]83[/TD]
[TD]83[/TD]
[TD]7672[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]132[/TD]
[TD]9[/TD]
[TD]14[/TD]
[TD]53[/TD]
[TD]84[/TD]
[TD]84[/TD]
[TD]7673[/TD]
[/TR]
</tbody>[/TABLE]

The above list goes on till 1000 rows. what i want to do is,i want to pick random samples of 8% from the whole data username wise.
if xxx user data is 250 in sheet 1 i want to take 8% sample which is 250*8/110 = 20 random samples.like wise i want to do it for all the users one by one and auto paste it in the sheet 2 like given below ( the whole row data)

[TABLE="width: 557"]
<colgroup><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]xxxx[/TD]
[TD]23[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]45[/TD]
[TD]76[/TD]
[TD]76[/TD]
[TD]7665[/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD]78[/TD]
[TD]6[/TD]
[TD]11[/TD]
[TD]50[/TD]
[TD]81[/TD]
[TD]81[/TD]
[TD]7670[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 557"]
<colgroup><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]zzzz[/TD]
[TD]25[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]47[/TD]
[TD]78[/TD]
[TD]78[/TD]
[TD]7667[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]56[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]49[/TD]
[TD]80[/TD]
[TD]80[/TD]
[TD]7669[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]243[/TD]
[TD]7[/TD]
[TD]12[/TD]
[TD]51[/TD]
[TD]82[/TD]
[TD]82[/TD]
[TD]7671[/TD]
[/TR]
[TR]
[TD]zzzz[/TD]
[TD]132[/TD]
[TD]9[/TD]
[TD]14[/TD]
[TD]53[/TD]
[TD]84[/TD]
[TD]84[/TD]
[TD]7673[/TD]
[/TR]
</tbody>[/TABLE]

when i do it manually using rand formula and filter options it takes much time, i'm wondering if i can automate these steps. since i do this work every day it eats lot of my time. any help on this is much appreciated !!!.

Thanks in advance

 

[ParamRay]

Assuming your data starts in cell A1 with headers in the top row, this should work:

Public Sub PickSample()
  Const strSHEET_NAME As String = "Sheet1" '<-- Change to the name of the
  Const dblSAMPLE_RATE As Double = 0.08    '    sheet containing the data
  Const intNUM_COLUMNS As Integer = 8 '<-- This is the number of
  Dim avarSampleData() As Variant     '    columns in the data
  Dim astrColumnNames() As String
  Dim lngSampleSize As Long
  Dim lngUserCount As Long
  Dim lngRandomNo As Long
  Dim i As Integer
  Dim j As Long
  
  ReDim astrColumnNames(1 To intNUM_COLUMNS)
  astrColumnNames(1) = "username"
  astrColumnNames(2) = "work"
  astrColumnNames(3) = "number"
  astrColumnNames(4) = "link"
  astrColumnNames(5) = "worked on"
  astrColumnNames(6) = "duration"
  astrColumnNames(7) = "find"
  astrColumnNames(8) = "no find"
  
  With ThisWorkbook.Sheets(strSHEET_NAME)
    lngUserCount = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
    lngSampleSize = CLng(lngUserCount * dblSAMPLE_RATE)
    ReDim avarSampleData(1 To lngSampleSize, 1 To intNUM_COLUMNS)
    
    For j = 1 To lngSampleSize
      lngRandomNo = Int(lngUserCount * Rnd() + 1)
      For i = 1 To intNUM_COLUMNS
        avarSampleData(j, i) = .Cells(lngRandomNo + 1, i).Value
      Next i
    Next j
  End With
  
  With ThisWorkbook.Sheets.Add
    .Range("A1").Resize(, intNUM_COLUMNS) = astrColumnNames()
    .Range("A2").Resize(lngSampleSize, intNUM_COLUMNS).Value = avarSampleData()
  End With
End Sub

 

[ebineg]

hi gpeacock ,

The code was great , it took 8% samples from the whole sheet regardless to the usernames .
The thing is i wanted to take samples separately for each user from the same sheet1( lets assume the sheet 1 contains 1000 rows and we have 3 users, datas of users is mixed up , assume data of user xxx are present in 350 different rows, data of user yyy are present in 500 different rows and data of user zzz are present in 150 different rows so total = 1000 rows.
Now I din't want to take samples for that 1000 rows,instead i want to take samples for each user . first 8% sample for xxx user (350 rows),then followed by 8% sample for yyy user (500 rows), at last 8% sample for zzz user (150 rows).
so when i run the macro , i should be able to get 8% random samples for each user seperatly in the same consolidated sheet.

i hope this explains my case. it will be really helpful if i get code for the above logic.

Thanks in advance.

 

[ebineg]

Hi Guys,

Could anyone help me on the above request?

Thanks.