Swap contents of each cell with contents of cell below it

[stellavantouz]

I'm looking for a formula, or whatever, to swap the contents of each cell with the contents of the cell below it.

In a list of names: Sally, Petra, Joshi, Maya, Sunil, Karen
I want to end up with: Petra, Sally, Maya, Joshi, Karen, Sunil

Or, a column of numbers and I want to swap each number with the number in the cell below it so that the number in A2 ends up in A1 and the number originally in A1 in A2. Then to swap A4 and A5 etc...

Thanks in advance for helping reduce my breathtaking ignorance for not knowing how this is done.

BEFORE
A1 21313
A2 34435
A3 65756
A4 3457
A5 77
A6 3234

AFTER
A1 34435
A2 21313
A3 3457
A4 65756
A5 3234
A6 77

 

[NARAYANK991]

Hi ,

If you are looking for a formula , note that it cannot be done in the same column which has your data ; if your data is in column A , the formulae will be in any other column , say column B , and column B will have the desired output.

If you want the data to be rearranged within the same column , it can be done only by using VBA.

Narayan

 

[stellavantouz]

Thanks for the response.

I have no objection to having the results in a different column. In fact, I much prefer accomplishing this with a formula than a macro because it's a later of complication that is probably overkill.

Again, I appreciate any help identifying a formula to swap cell contents....

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[bosco_yip]

Another option,

1] Data in A1:A6

2] In B1, formula copy down :

=OFFSET(A1,MOD(ROWS($1:1),2)*2-1,)

Regards
Bosco

 

[stellavantouz]

Another option,

1] Data in A1:A6

2] In B1, formula copy down :

=OFFSET(A1,MOD(ROWS($1:1),2)*2-1,)

Regards
Bosco

Thank you very much! This is exactly what I hoped for! I would not have come up with this solution myself... It's also a fine demonstration of the OFFSET function which I could never get quite righ. So thanks to the example I can already see how I can apply this elsewhere.

Thanks again, I really appreciate it.

 

[stellavantouz]

Hi ,

See if this is OK.

Narayan

This is perfect, thanks for taking the time to solve.

Both this reply and the other one offer a good solution. I also learned the OFFSET function's proper usage. Your approach approach
use the MOD function to "test" the cell row referenece for a nonzero result.

Solutions that others come up with seem simple in retrospect, I guess that's a corollary to hindsight being 20/20!

Thanks again.