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

ebineg

New Member
Joined
Feb 24, 2016
Messages
39
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 :) :) :)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Assuming your data starts in cell A1 with headers in the top row, this should work:
Code:
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
 
Upvote 0
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.

 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top