Returning every 7th row of a column

[bondjel]

I have a row of 1836 consecutive dates in a column and I want a new column
with only every 7th row's date (the date beginning each week). How do I get
Excel to do this?

 

[Mike H]

Hi,

Assuming the date are in Col A try this formula dragged down

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

Now this assumes the data start in row 1. If it's another row then adjust
the last 1 in the formula to that row
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[bondjel]

Unless I did something wrong, I used this formula and it returned the 1st
date properly but did repeat by looping thru the whole column of dates.

 

[bondjel]

I should have said the formula did NOT repeat by looping thru the whole
column of dates

 

[Mike H]

Hi,

Put the formula in a cell and it will return the first value. You have to
drag the formula down to get the next values

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[bondjel]

Mike,

Perhaps I should also have included that the 1st date I want it to return is
in the 7th row not the 1st row. When I use your original formula:

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

it yields the date in A1 and then the date in A7 (the first one I want) but
then it returns the date in A13, then A19, etc. The dates after the second
one (A7) are all just one row short of the the desired one.

I think I've tried varying every variable in the formula and it keeps
missing by just one under the desired number of rows or just one over. If I
understood the VBA syntax I'd be able to solve it. What should I try next?

Jim

 

[Mike H]

Hi,

As I pointed out in my first post the ""last"" number is the start row so to
start on row 7 change that to a 7. Then if it's one row out change the 6 to a
7.

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[bondjel]

Mike,

Thank you SOOOOO much. I've spent nearly two days trying to figure this out
for myself. I didn't understand the syntax alright! I wasn't sure you really
meant last when you said "last". I just didn't get what each number in the
formula syntax was doing.

Jim

 

[Chip Pearson]

I have formulas to do this and other related tasks at
http://www.cpearson.com/Excel/EveryNth.aspx .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com