General discussion

Locked

Excel and Data Analysis Tool - Sampling

By tracyp ·
The Excel Data Analysis Tool for Sampling seems to be only for numerical data. What I have is a list of 3000 names, accounts, address, city, state, zip. I want to generate a sampling that will select 300 rows from this list by every nth row. I don't want a random sampling since I want to be able to recreate the same list several times. How would I do this?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel and Data Analysis Tool - Sampling

by DKlippert In reply to Excel and Data Analysis T ...

Try this macro. Create a blank column for a "Flag". When you run the macro, you will be asked for an interval and then an X will be placed in the Flag column at every Nth row. This can then be sorted or filtered.

Sub Selectnth()
'start at the top of the second column
'it is assumed the second column has data
'first column is the flag column.
intnth = InputBox("Enter the interval value")
'repeat until on a blank cell
Do Until ActiveCell.Value = ""
x = 1
For x = 1 To intnth
ActiveCell.Offset(1, 0).Range("A1").Select
Next
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.Value = "X"
ActiveCell.Offset(0, 1).Range("A1").Select
'move down a row
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Collapse -

Excel and Data Analysis Tool - Sampling

by tracyp In reply to Excel and Data Analysis T ...

Poster rated this answer

Collapse -

Excel and Data Analysis Tool - Sampling

by tracyp In reply to Excel and Data Analysis T ...

This looks like what I want. However there is some glitch. When I run in debug mode, I can go through and it does ask me to Enter the interval value, then I F5 and the Do Until ActiveCell.Value = "" is highlighted. Then when I F5 again it goes tothe End Sub line and skips the rest. I double checked my spacing, dots, etc and they seem to be all in the right places.

Collapse -

Excel and Data Analysis Tool - Sampling

by tracyp In reply to Excel and Data Analysis T ...

This looks like what I want. However there is some glitch. When I run in debug mode, I can go through and it does ask me to Enter the interval value, then I F5 and the Do Until ActiveCell.Value = "" is highlighted. Then when I F5 again it goes tothe End Sub line and skips the rest. I double checked my spacing, dots, etc and they seem to be all in the right places.

Collapse -

Excel and Data Analysis Tool - Sampling

by DKlippert In reply to Excel and Data Analysis T ...

There must be an empty column to the left of the data.
The macro assumes a label/field name in the first cell.
Select the first cell to start.
Step into, of course, is F8.
Works for me when run as is or in Debug mode.

Collapse -

Excel and Data Analysis Tool - Sampling

by tracyp In reply to Excel and Data Analysis T ...

Thank You. I guess I should really read the instructions.

Collapse -

Excel and Data Analysis Tool - Sampling

by tracyp In reply to Excel and Data Analysis T ...

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums