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

[tkushner]

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!

[#2]

@tkushner 

=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.

[#3]

@tkushner 

=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.

[#4]

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

[#5]

@OliverScheurich --Thank you!