Returning every 7th row of a column

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?
 
M

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.
 
B

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.
 
B

bondjel

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

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.
 
B

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
 
M

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.
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top