Status: Solved
Priority: Medium
Security: Public
Views: 281
Last Modified: 2012-06-21

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

Carlynne

Asked: 2011-03-07

Advertise Here

Microsoft Excel

1 Solution

LVL 10

MakriniCommented: 2011-03-07

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

CarlynneAuthor Commented: 2011-03-07

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

LVL 10

MakriniCommented: 2011-03-07

Data-Sample-1-.xlsm

Try this

CarlynneAuthor Commented: 2011-03-07

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

LVL 10

MakriniCommented: 2011-03-07

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

CarlynneAuthor Commented: 2011-03-07

Hi,

Ah, yeah, that makes sense.

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

carlynne

CarlynneAuthor Commented: 2011-03-08

thank you!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Extracting Every 19 Records in Excel to Create Random Sample

Join & Write a Comment Already a member? Login.

Featured Post