Enter every nth cell item

[Guest]

Hello,
Using Excel 2003. I am looking for a formula that will grab every 4th item
in a column and then paste it to a new column (or worksheet). For example,
cells A1, A2, ... A20 contain first names. I want to grab every 4th name in
the column and be copied to column D. Thanks for your help.

 

[Frank Kabel]

Hi
in D1 enter
=OFFSET($A$1,(ROW()-1)*4,0)
and copy down

 

[Guest]

Although you can do this with VBA you can also do it using a formula

if you meant extract what's in A1, A5, A9 and so on


=OFFSET($A$1,ROW(1:1)*4-4,)

copy down

if you meant A4, A8, A12 and so on

=OFFSET($A$1,ROW(1:1)*4-1,)


Regards,

Peo Sjoblom

 

[Guest]

Thanks, Frank for you quick response. Works great!
Sonya

 

[Aladin Akyurek]

If entered in D1...

=INDEX(A:A,(ROW()-ROW($D$1))*4+1)

which starts copying with the item in A1.

=INDEX(A:A,(ROW()-ROW(D$1))*4+4)

which starts copying with the item in A4.

These formulas are robust against inserting rows before the data and/or
before the formula cell.