CODEWITHSUNDEEP
excel
hithesh
hithesh

Reputation: 439

Swapping adjacent rows in excels

How do you sawp adjacent rows in excel. In the attachment below, "col" is my data. How do I sawp adjacent rows, so that I get the data in "result" column. enter image description here

Upvotes: 1

Views: 68

Answers (3)

Micah Lindstrom
Micah Lindstrom

Reputation: 365

Avoid VBA and volatile formulas using Excel's new spilling formulas (LAMBDA, LET, etc., see this and that). Copy this formula, modifying the B3:B8 range as needed in two places:

=CHOOSEROWS(B3:B8, TOCOL(CHOOSECOLS(SEQUENCE(ROWS(B3:B8)/2,2),2,1)))

The LET formula lets you input the range in just one place, and makes it a little more human-readable:

=LET(
myRange, B3:B8,
swapIndices, TOCOL(CHOOSECOLS(SEQUENCE(ROWS(myRange)/2,2),2,1)),
CHOOSEROWS(myRange, swapIndices)
)

formula example with LET

And lastly here's an Excel LAMBDA formula which you can add to the Name Manager so that you can call something as simple as =swap(B3:B8) within a cell. It's also improved to handle an odd number of cells (return the last row as-is) and optionally swap columns instead of rows:

=LAMBDA(rangeToSwap,[swapColumns], LET( myRange, IF(swapColumns, TRANSPOSE(rangeToSwap), rangeToSwap), swapIndices,TOCOL(CHOOSECOLS(SEQUENCE(ROWS(myRange)/2,2),2,1)), swapRows,CHOOSEROWS(myRange,swapIndices), lastRow, TAKE(myRange,-1), returnSwap, IF(MOD(ROWS(myRange),2),VSTACK(swapRows,lastRow),swapRows), IF(swapColumns, TRANSPOSE(returnSwap), returnSwap)))

swap formula examples

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

For a non-volatile formula, independent of the location of the initial range (or where you want your resuts), you can try:

=INDEX(myRng,2*MOD(ROWS($1:1),2)-1+ROWS($1:1))

enter image description here

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 36880

Use OFFSET() and MOD() combinedly. Try-

=OFFSET(A2,(2*-MOD(ROW(),2))+1,0)

enter image description here

Upvotes: 1

Related Questions