• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Extracting Every 19 Records in Excel to Create Random Sample

Hi,

I'd like to take my spreadsheet and extract every 19 records starting with record 1069 to create a random sample.  Should I create a macro or function to tell Excel to select every 19 records?

Please see attached worksheet.

Thanks,
carlynne
--S1-Select.xlsx
0
Carlynne
Asked:
Carlynne
  • 4
  • 3
1 Solution
 
MakriniCommented:
The file you attached appears to be corrupted (and has 0 bytes)

Simply run a Macro to copy every 19th row

for i = 1 to 64000 step 19
   Rows(i).copy
Next i

Open in new window

0
 
CarlynneAuthor Commented:
Hi,

Thanks very much for your reply.

I am not sure how to insert a macro. The code should also start at record 1069.

I'd really appreciate further assistance if possible.  Here's the sheet again.

Thanks so much!

Carlynne


Data-Sample.xlsx
0
 
MakriniCommented:
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CarlynneAuthor Commented:
Hi there,

Thanks so much for your quick reply.  The code does select the correct first record 1069 but it only selects 135 households. I need a sample size of 192.

Also, I have to run a simple coding on 8 more spreadsheets, so I need to be able to insert the macro. I can insert a Marco. I just need to be able to see the code in a separate worksheet.

Thanks so much! You're a star!

Carlynne
0
 
MakriniCommented:
From 1069 to 3615 (the provided data) = 2546 records

Divide that by 19 and you have 35....  You could try with 18

The code is below... - to use press alt-f11 (to open VBA).  Use insert from the menu and insert a module..

In Module 1 paste the below.

To do it on different sheets change "Sheet1" and "Sheet2" to the sheets you want to do it on

To change sample size, change the "For i = 1069 To lastrow Step 19
" to step 18 or whatever
Sub tester()
lastrow = Sheets("Sheet1").Range("A64000").End(xlUp).Row
u = 1
Application.ScreenUpdating = False
For i = 1069 To lastrow Step 19
   Rows(i).Copy
   Sheets("Sheet2").Rows(u).PasteSpecial
   Application.CutCopyMode = False
   u = u + 1
Next i
Application.ScreenUpdating = True

End Sub

Open in new window

0
 
CarlynneAuthor Commented:
Hi,

Ah, yeah, that makes sense.  

I really appreciate your help. I will try this and let you know if it works:)

carlynne
0
 
CarlynneAuthor Commented:
thank you!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now