How to return every Nth value in a column in Excel?

October 26, 2009 9:45 AM

I am trying to pull values from one sheet into another master sheet for daily trending. The OFFSET function works for a single cell, but I can't figure out how to copy that formula to the rest of the column.

Here's an example.
The total sheet uses dates and revenue. The values come from second sheet, every 5th cell in column D, starting with D5.

Date Revenue
10/19/09 (Second Sheet, D5)
10/20/09 (Second Sheet, D10)
10/21/09 (Second Sheet, D15)
10/22/09 (Second Sheet, D20)

I literally have hundreds of these, so picking each value would be tedious and prone to error. Any help is appreciated.

posted by malcommc to work & money (13 answers total)

You could use the INDEX formula:
=INDEX(Sheet2!D:D,(A2-40104)*5,1) would go in B2

40104 in this case means 10/18/2009, and what you are doing is subtracting 10/18 from the date in A2 and multiplying the result by 5 to get your row reference.
posted by soelo at 10:09 AM on October 26, 2009

I'd use the INDEX and ROW() functions. Assuming that your "summary sheet" starts in cell A2, and that your "detail sheet" is Sheet2, with the data in column A put this formula in cell A2 of the "summary sheet" and copy it down:

=INDEX(Sheet2!A:A,(ROW()-1)*5)

The ROW() function just returns the row number of the cell in which the formula is placed. So essentially what Excel calculates is:

=INDEX(Sheet2!A:A,5)    ((2-1)*5)=5
=INDEX(Sheet2!A:A,10)   ((3-1)*5)=10

posted by Doofus Magoo at 10:10 AM on October 26, 2009

When you are composing the INDEX formula, use the Syntax 1 for array, assuming Excel 2003.
posted by soelo at 10:11 AM on October 26, 2009

Sorry, I am on 2003 and can't get either of these to work correctly. I did get the right result for the formula that uses the date method that soelo laid out, but I couldn't copy that down the row correctly.
posted by malcommc at 11:08 AM on October 26, 2009

I don't know if it will be flexible enough, but this is the way I jump thru different rows and it does work in 2003

- First fill a column somewhere out of the way with the row numbers you need, this would be like:
5
10
15
etc.

- Then use the adress function to create the adress you want to search:

in the cell you want to display the results:

ADRESS(rownumber,columnnumber;;;"Sheet2")

in which row number directs to the first cell of the list of step 1, and column is the number of the column you need to get from sheet2

if this works and the cell displays the correct adress, put Indirect( ) around the whole adress function and you should get the value from the cell you are looking for


Hope this helps
posted by PaulZ at 11:40 AM on October 26, 2009

but I couldn't copy that down the row correctly.

What wasn't copying correctly?
posted by Doofus Magoo at 1:24 PM on October 26, 2009

I could get soelo's formula to work in the first cell, but could not get the "every nth" part to work, so the correct values wouldn't line up to the right date. I probably need to create this in a blank workbook with generic numbers then upload it somewhere as a better example of what problems I'm having.
posted by malcommc at 2:42 PM on October 26, 2009

I'm not really sure what you mean by not being able to get the "every nth" part to work -- it should just be a matter of copying (not retyping) that formula from B2 down to B3:B100 or however far down you want it to go.
posted by Doofus Magoo at 4:59 PM on October 26, 2009

Here is a link (warning, this will download a sample .xls file) to a basic example of what I'm trying to solve. In this workbook, the first sheet is going to pull values from the second sheet. I've only included about a week's worth of data.

The first sheet's data begins at A2, with the following column headings:
Date | Apple Sales | Orange Sales | Banana Sales | Pear Sales | Peach Sales

Assuming that A2 would begin with 10/01/09, and the dollar amount for Apple Sales comes from the second worksheet, cell C2. The next time that Apple Sales shows back up in my example is on the second worksheet, cell C9 (every 7 rows in column C, there will be a dollar amount for Apple Sales for the next calendar day)

Is INDEX still the right way to go about writing the formula to populate the first worksheet? I can't change the way the data is output on the second sheet, but I do know it will always be a set number of rows between values. In this sample case, 7 rows.
posted by malcommc at 8:29 PM on October 26, 2009

Here's a revised version with the formulas populated. Assuming this was the worksheet that you were trying to copy soelo's formula into, the reason it probably wasn't working correctly was that the data for each category aren't repeated every five rows -- they're repeated every seven rows (e.g., apples are in row 2, 9, 16, etc.) because although you have five categories (apple, orange, banana, pear, peach), you also have the blank date row and the total row that you have to take into account.

So far apples, the formula looks like:

=INDEX(Data!C:C,(ROW()-1)*7-5)

When you put that formula in cell B2, it resolves to:

=INDEX(Data!C:C,(2-1)*7-5)

... or:

=INDEX(Data!C:C,2)

That is, pulling the data from row #2 of column C:C in the "Data" sheet.

For oranges, you'd increment the formula by one from what you used for apples (note that the 5 at the end changed to a 4):

=INDEX(Data!C:C,(ROW()-1)*7-4)

When you put that formula in cell C2, it resolves to:

=INDEX(Data!C:C,(2-1)*7-4)

... or:

=INDEX(Data!C:C,3)

That is, pulling the data from row #3 of column C:C in the "Data" sheet.

Then you do something similar for bananas; put the formula in column D, and change the last part of the formula to -3.

Then you do something similar for pears; put the formula in column E, and change the last part of the formula to -2.

Then you do something similar for peaches; put the formula in column F, and change the last part of the formula to -1.

Once you have the formula in B2:F2, you just copy it down as far as you want to go.
posted by Doofus Magoo at 4:06 AM on October 27, 2009

Thank you very much! It took me a bit to finally see how this was working, but now that I understand it, I'm able to use it for a lot of various worksheets. Really, really appreciate everyone's help in this thread!
posted by malcommc at 7:39 AM on October 27, 2009

I cannot believe I just signed up to answer this question, and in the meantime you accepted a different answer... bummer! At least going forward I will get to chime in on all the human relations questions ;)

From your example, here's the forumla I put in cell B2:
=VLOOKUP(B$1,OFFSET(Data!$A:$C,MATCH($A2,Data!$A:$A,0),1,6,2),2,0)

You should just be able to copy it over and down on the summary sheet... the offset function creates a range specific to your date, and the VLOOKUP function looks up the specific item.
posted by _Silky_ at 7:45 AM on October 27, 2009

Thanks Silky, I'll give that a try too. I was wondering if VLOOKUP might be used. Don't worry, I'm sure you'll have plenty of opportunity to answer many more of my excelnoob questions!
posted by malcommc at 9:00 AM on October 27, 2009