Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
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
On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation)
David Miller (dlmille)
Synopsis
In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.