Enter every nth cell item

G

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

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
 
A

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.
 

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