Returning every 7th row of a column
 

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?

Returning every 7th row of a column
 

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" wrote:

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?

Returning every 7th row of a column
 

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.

"Mike H" wrote:

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" wrote:

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?

Returning every 7th row of a column
 

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

"bondjel" wrote:

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.

"Mike H" wrote:

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" wrote:

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?

Returning every 7th row of a column
 

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" wrote:

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

"bondjel" wrote:

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.

"Mike H" wrote:

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" wrote:

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?

Returning every 7th row of a column
 

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" wrote:

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" wrote:

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

"bondjel" wrote:

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.

"Mike H" wrote:

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" wrote:

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?

Returning every 7th row of a column
 

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" wrote:

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" wrote:

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" wrote:

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

"bondjel" wrote:

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.

"Mike H" wrote:

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" wrote:

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?

Returning every 7th row of a column
 

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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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

"bondjel" wrote:

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.

"Mike H" wrote:

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" wrote:

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?

Returning every 7th row of a column
 

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



On Thu, 18 Mar 2010 06:20:01 -0700, bondjel
wrote:

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?