# Formula for moving a range of cells in one row to a new row

Copper Contributor

# Formula for moving a range of cells in one row to a new row

Hello All,

On the small sample inserted below, you will see that every student has a unique number in Column A and each student has two rows of data.  I would like to move the data so that every student has only one row of data.

For example, the first student is student number 150 (A2 and A3). I would like to move the data from cell range G3:L3 to M2:R2.  I want to do this for every student.

How do I do this?  Thank you for your help!

4 Replies

# Re: Formula for moving a range of cells in one row to a new row

=IF(COUNTIF(\$A\$2:\$A2,\$A2)=1,XLOOKUP(\$A2,\$A\$2:\$A\$11,G\$2:G\$11,,,-1),"")

If you have access to XLOOKUP you can apply this formula. The formula is in cell M2 and copied across range M2:R11 in the example.

# Re: Formula for moving a range of cells in one row to a new row

=IFS(

DROP(REDUCE("",SEQUENCE(ROWS(A2:A21)),LAMBDA(u,v,VSTACK(u,SUM(N(TAKE(A2:A21,v)=INDEX(A2:A21,v)))=1))),1),
CHOOSEROWS(G2:L21,BYROW(A2:A21,LAMBDA(r,XMATCH(r,A2:A21,,-1)))),

1,

"")

With Excel for the web or Office 365 you can spill the result with this formula.

# Re: Formula for moving a range of cells in one row to a new row

alternatively make a whole new table using:
=WRAPROWS(TOCOL(A2:L99),24,"")

then you could choose to copy->paste values and then delete redundant columns

# Re: Formula for moving a range of cells in one row to a new row

@OliverScheurich --Thank you!