Retrieve value from alternating rows

[theraindew]

I have a sheet where i need the contents of A2 to appear in B3 and contents of A3 to appear in B2. The contents could be numbers or text or a mix of both. I can do this by just referencing the alternate cells, but there are about 900 rows and the count changes everyday. Is there an easier way to achieve this using a formula to enter in B2 and drag down or using VBA?

ck.xlsx
	A	B	C
1	Item	Result	Formula for Reference
2	Apple	Orange	Orange
3	Orange	Apple	Apple
4	Grape	Apricot	Apricot
5	Apricot	Grape	Grape
Sheet1

Cell Formulas
Range	Formula
C2,C4	C2	=A3
C3,C5	C3	=A2

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Item	365	2016
2	Apple	Orange	Orange
3	Orange	Apple	Apple
4	Grape	Apricot	Apricot
5	Apricot	Grape	Grape
6	Pear	Mango	Mango
7	Mango	Pear	Pear
Main

Cell Formulas
Range	Formula
B2:B7	B2	=LET(d,A2:A7,s,SEQUENCE(ROWS(d)),INDEX(d,MOD(s,2)+s-ISEVEN(s)))
C2:C7	C2	=INDEX($A$2:$A$7,MOD(ROWS(C$1:C1),2)+ROWS(C$1:C1)-ISEVEN(ROWS(C$1:C1)))
Dynamic array formulas.

 

[Peter_SSs]

Like this?

23 06 29.xlsm
	A	B
1	Item	Result
2	Apple	Orange
3	Orange	Apple
4	Grape	Apricot
5	Apricot	Grape
Swap

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX(A:A,ROW()+IF(ISODD(ROWS(B$2:B2)),1,-1))

 

[theraindew]

Thank you Fluff and Peter, exactly what I wanted. Both of your formulas work.

 

[Rick Rothstein]

For XL365, this formula would also work...

=TOCOL(CHOOSECOLS(WRAPROWS(A2:A7,2),2,1))

 

[theraindew]

For XL365, this formula would also work...

=TOCOL(CHOOSECOLS(WRAPROWS(A2:A7,2),2,1))

Thanks Rick. Works great.
Cool functions!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.