Pulling data from one column to another column with a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jp - 12 Nov 2007 22:37 GMT

Say I have the following sheet

A
1 c
2 a
3 b
4 c
5 d
6 a
7 c
8 a
9 b
10 c

How can I create a column in B that displays the values in A1, A3, A5,
A7, A9. Excel will not allow me to just put "=A1" in B1 and "=A3" in
B2 and then drag that pattern down. It does not recognize that
pattern. Help! The answer sheet needs to look like this:
A B
1 c c
2 a b
3 b d
4 c c
5 d b
6 a
7 c
8 a
9 b
10 c

Reply to this Message

Gord Dibben - 13 Nov 2007 00:07 GMT

In B1 enter =A1

In B2 enter this and copy down.

=OFFSET($A$1,2*ROW()-2,0)

Gord Dibben MS Excel MVP

>Say I have the following sheet
>
[quoted text clipped - 25 lines]
>9 b
>10 c

Reply to this Message

jp - 13 Nov 2007 22:40 GMT

Thanks so much for your help! That is a tremendous time saver!

Reply to this Message

carlo - 13 Nov 2007 00:14 GMT

> Say I have the following sheet
>
[quoted text clipped - 25 lines]
> 9 b
> 10 c

paste this formula in A1 and copy it down:

=INDIRECT("A"&(ROW()-1)*2+1)

if you want to show "" if column A is empty you can do that

=IF(INDIRECT("A"&(ROW()-1)*2+1)="","",INDIRECT("A"&(ROW()-1)*2+1))

hth

Carlo

Reply to this Message

RagDyer - 13 Nov 2007 01:23 GMT

Non-volatile approach:

=INDEX(A:A,2*ROWS($1:1)-1)

Enter anywhere, and copy down as needed.

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Say I have the following sheet
>
[quoted text clipped - 25 lines]
> 9 b
> 10 c

Reply to this Message