Returning every 7th row of a column

  • Thread starter Thread starter bondjel
  • Start date Start date
B

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?
 
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.
 
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.
 
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.
 
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
 
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.
 
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
 
Back
Top