Automate finding every 15th record

[jlo]

I have a spreadsheet and I am trying to develop a list from that spreadsheet.
I need Excel to find the data in every 15th row in column A.

For instance Column A is Name, Column B is Address. I want to generate a
list of every name in Column A for every 15th record.

Would I need a formula, macro or code?

Thanks in advance.

 

[Gary Keramidas]

here's on way:

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

With ws
For i = 1 To lastrow Step 13
MsgBox .Range("A" & i) & " " & .Range("B" & i)
Next
End With
End Sub

 

[Bob Umlas]

Enter A2 (no equal sign), and below it put A17 (no equal sign).
Select both cells, use the fill handle & you'll see A32, A47, etc
Select all these, use edit/replace & replace A with =A and you're done.
Bob Umlas
Excel MVP

 

[mark]

The answer to your question about whethe you need a formula, macro, or code,
most likely depends upon how often you need to do it. If you only need to do
it once, formulas will do it nicely. If you need to do it a bunch of times,
you'll want to automate it with code.

Here are two formulas that may help:

=ROW(A1)
=MOD(A1,15)

=ROW will tell you the row number of eacy line.

=MOD will result in 0 (the remainder, when the row number is divided by 15)
each time it hits a 15th row.

Hope that helps.
Mark

 

[Bob Phillips]

You can do it with a formula

=INDEX(A:A,(ROW(A1)-1)*15+1)

and so on

 

[ShaneDevenshire]

Hi,

don't need a formula or code, Excel has a feature to do this:
Choose Tools, Add-in, and check Analysis ToolPak if necessary
1. Choose Tools, Data Analysis, Sampling, OK
2. Indicate your colum A range in the Input Range box, if there is a title
in the top row of your selection check Labels, Choose Periodic and enter 15
for the Period, click Output Range or whatever, and indicate the top cell of
the output range in the adjacent box. Click OK.

 

[Rick Rothstein]

As a follow up to Bob's formula, you can carry the math out on this type of
mathematical construction to simplify it a little...

=INDEX(A:A,15*ROW(A1)-14)

To get the constant (14 in this case) to adjust the formula for the starting
row, just subtract the starting row you want from 15. For example, to get
the constant for starting on row 1... 15-1=14. If you wanted to start on row
3, then 15-3=12 which means you would replace the 14 in the above formula
with 12 to get the list to start at row 3.

 

[jlo]

Awesome! Thanks.

The answer to your question about whethe you need a formula, macro, or code,
most likely depends upon how often you need to do it. If you only need to do
it once, formulas will do it nicely. If you need to do it a bunch of times,
you'll want to automate it with code.

Here are two formulas that may help:

=ROW(A1)
=MOD(A1,15)

=ROW will tell you the row number of eacy line.

=MOD will result in 0 (the remainder, when the row number is divided by 15)
each time it hits a 15th row.

Hope that helps.
Mark