Listen to shijinn and don’t ask. Simply picking every 10th row is not good methodology.
True. The whole purpose of your exercise is to find a random sampling of 10%. Not “every tenth row”.
Here’s another thought: How is the original list sorted? Name? Address? You could take the first 500 rows based on nearly any sorted column and come up with a random sampling. Maybe even simply the first 500 rows of the original list. You might not have needed to do any of this weird formula stuff!
While every tenth row is not a good way to pick a sample, a macro would be much simpler.
Sub Macro1()
x = 1
For x = 1 To 5000
Cells(x, 1).Select
Selection.EntireRow.Delete
x = x + 10
Next x
End Sub
X is the first row of your spreadsheet (if it has a header then you can change it to 3 or 5 or whatever you need). The 5000 is the row size. If it was 700 people, change it to 700.
You can copy and paste this right in. TOOLS>MACRO>VISUAL BASIC EDITOR. In the Visual Basic Editor, INSERT>MODULE. The window that pops up, paste in the above macro and you are done. Go back to the Excel, TOOLS>MACRO>MACRO and you should have only one choice, Macro1.
I ended up doing the random number, sorting by that, and pulling out the first 500 entries to form the list. Which as a bonus, is a true random sample, which the every tenth whatever wouldn’t have been.
Thanks again everyone.
Memo to self: if I ever ask for advice, be sure I’m asking for what I really want, not how I assumed it would be done. It’s hard to figure out if I’m unfamiliar with the tool, but it helps a lot.
That’s not a dig, spectrum, it’s just something useful for me, and I think others, to remember
OK, instead of starting a new Excel thread, I’ll put this here.
I’ve lost the macro toolbar. It’s just not there when I try to record a macro, and without it, I don’t know how to make sure that I’m recording in relative reference instead of absolute.
Ideas?
Lost the macro toolbar? :dubious:
Unless I’m misunderstanding you, you should be able to go to the menu VIEW>TOOLBARS>VISUAL BASIC and it should pop up.
No, I mean the little one with the button to turn on the relative reference function for recording your macros. I see what you mean, but that one does not have a relative reference button.
There was a little two button bar that would come up when I recorded a macro, but for no discernable reason, it’s gone. It simply doesn’t appear any more.
Gotcha! I thought that may have been what you were talking about.
VIEW>TOOLBARS>CUSTOMIZE and go to the TOOLBARS tab. Check on the STOP RECORDING checkbox and you should get your toolbar back, complete with the Relative Reference button.
Thank you thank you thank you thank you.
Ahhhhhhhhhhh…