Forum Discussion
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
=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.
OliverScheurich --Thank you!
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
=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.
