hi, I need to turn a matrix (?) into three columns, example attached
hi, I need to turn a matrix(?) into three columns, i know it might seem basic stuff, but i can't get my head around it.
basically a 3x3 should become 9 lines of details.
i need a formula that can do that
example attached -first tab the matrix, then the wished result in tab no 2
thank you so much,
Butterlord
aka
Pietro
Last edited by ButterLord; 09-29-2020 at 10:36 AM.
If origin!B3:F7 were named Matrix, then in the results worksheet,
B2: =INDEX(origin!$B$1:$Z$1,ROUNDUP(ROWS(B$2:B2)/COLUMNS(Matrix),0))
D2: =INDEX(origin!$A$3:$A$27,1+MOD(ROWS(D$2:D2)-1,ROWS(Matrix)))
F2: =INDEX(Matrix,1+MOD(ROWS(F$2:F2)-1,COLUMNS(Matrix)),ROUNDUP(ROWS(F$2:F2)/COLUMNS(Matrix),0))
Select B2:F2 and fill down into B3:F26.
hi hrlngrv, i am doing it wrong, could you please add the excel solution so i can understand it better? thx
Please try at
B2
=INDEX(origin!B1:F1,SEQUENCE(ROWS(origin!B3:F7)*COLUMNS(origin!B3:F7),,,1/ROWS(origin!B3:F7)))
D2
=INDEX(origin!A3:A7,MOD(SEQUENCE(ROWS(origin!B3:F7)*COLUMNS(origin!B3:F7))-1,ROWS(origin!B3:F7))+1)
F2
=INDEX(origin!B3:F7,MATCH(D2#,origin!A3:A7,),MATCH(B2#,origin!B1:F1,))
If you subscribe of MS insider, There is new Let Function
=LET(d,origin!B3:F7,r,ROWS(d),s,SEQUENCE(r*COLUMNS(d))-1,
CHOOSE({1,2,3,4,5},INDEX(origin!B1:F1,s/r+1),"",INDEX(origin!A3:A7,MOD(s,r)+1),"",INDEX(d,MOD(s,r)+1,s/r+1)))
Crossposting (italian forum)
Aim high or don't even try.
---------------------------------
If your question has been answered, don't forget to mark the thread as SOLVED.
If you find an answer helpful, click on the star icon at the bottom of the post.
See this workbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks