For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Course Of The Month
8 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Extracting Every 19 Records in Excel to Create Random Sample
Posted on 2011-03-07
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
7 Comments
The file you attached appears to be corrupted (and has 0 bytes)
Simply run a Macro to copy every 19th row
Simply run a Macro to copy every 19th row
for i = 1 to 64000 step 19
Rows(i).copy
Next i
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
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
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
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
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
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
Featured Post
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 3 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.