Help One Way did not work

A

Arlene

The reply to my problem below did bring the variable over to column b,
however, the fill option repeats the same variable in row 1 and does not
extract the data in every 3rd row. What do you mean by copy down? nine
thousand records!

One way:

B1: =INDEX(A:A,(ROW()-1)*3+1)


Copy down.
 
C

Chrynoble

to cause the current formula to "auto-fill" down the side of another column,
simply enter the formula in the first row, and then double click the small
dark box in the lower right of the cell.

Was this your question?
 
R

Roger Govier

Hi Arlene

JE gave you the perfect answer, but perhaps you have not understood its
operation.
The formula is telling Excel to Index the whole of column A, so that it
knows the numeric position of each value in the column, then take the value
corresponding to the value after the comma in the formula.

ROW() returns the row number of the cell in which the formula exists i.e. 1
when in B1
so, (ROW()-1) = (1 -1) = 0 * 3 = 0 + 1 = 1
so in B1 Index returns the first value in column A

When you copy the formula from B1 to B2, then ROW() = 2
so, (ROW()- 1) = (2 -1) = 1 * 3 = 3 + 1 =4
so in B2 Index would return the 4th value in column A.

As you copy the formula down column B, so the values extracted will be the
1st, 4th, 7th etc.

You can copy down by hovering the cursor over the bottom right corner of
cell B1 until the cursor forms a small solid black cross. Dependant upon
your version of Excel, double clicking the cross will fill down through the
same range that holds data in column A, or, holding the left mouse button
down as you drag the mouse down through B2 to as far as you wish, will copy
the formula down.

Regards

Roger Govier
 
Top