Solved by Van Dona Help needed with transporting data from Sheet1 to Sheet 2 In sheet2
Solved by Van Dona
Help needed with transporting data from Sheet1 to Sheet 2 In sheet2
Replies
I am thinking about its solution, if you get its solution please share it.
If you have Office 365, try.. =LET(n,A10:A15,x,D2:O7,y,D10:O15,yf,TAKE(y,,1),yc,DROP(y,,1),tbl,TRANSPOSE(WRAPROWS(TOCOL(HSTACK(IFNA(n,yc),IFNA(yf,yc),CHOOSEROWS(DROP(x,,1),XMATCH(yf,TAKE(x,,1))),yc),,1),ROWS(n)COLUMNS(yc))),SORT(FILTER(tbl,(INDEX(tbl,,3)<>0)(INDEX(tbl,,4)<>0))))
u can also create a user-defined function? (i don't know what to call it) using lambda if u'll use the formula several times on ur worksheet.. create a defined name for below formula and name your function, i name it "MatchUnpivotArray"
=LAMBDA(Invoices,Array1,Array2,LET(n,Invoices,x,Array1,y,Array2,yf,TAKE(y,,1),yc,DROP(y,,1), tbl,TRANSPOSE(WRAPROWS(TOCOL(HSTACK(IFNA(n,yc),IFNA(yf,yc),CHOOSEROWS(DROP(x,,1),XMATCH(yf,TAKE(x,,1))),yc),,1),ROWS(n)COLUMNS(yc))), SORT(FILTER(tbl,(INDEX(tbl,,3)<>0)(INDEX(tbl,,4)<>0)))))
Van Dona thanks very much Can it be expanded for a larger number of codes?
Tamara Mitrovic haven't tried it but i guess it work, ucan select however many rows or columns, but both rectangular range should be same dimension.
Van Dona the range is the same but when the field is empty the formula does not work
Tamara Mitrovic by field u mean the "NESSD006-009" column? If it is, yeah the formula wont work, because some formula inside are referencing that column.
Van Dona Yes. I mean the column where the codes are. Can it be solved?
Tamara Mitrovic try this.. =LET(x,D2:O7,y,D10:O15,yf,TAKE(y,,1),yc,DROP(y,,1),tbl,TRANSPOSE(WRAPROWS(TOCOL(HSTACK(IFNA(yf,yc),CHOOSEROWS(DROP(x,,1),XMATCH(yf,TAKE(x,,1))),yc),,1),ROWS(y)COLUMNS(yc))),SORT(FILTER(tbl,(INDEX(tbl,,2)<>0)(INDEX(tbl,,3)<>0))))
Van Dona don't work
Tamara Mitrovic is this what you're after?
Also, =HSTACK(TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,2),TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,-1))
Samir Shaker thanks very much Can it be expanded for a larger number of codes?
Tamara Mitrovic Only change the Range..thats it
I expand the range but when the field is empty the formula does not work
Tamara Mitrovic screebshot pls?
Hi, I have solved it with simple excel formula; 4 lines example as under. For your understanding purpose I will make a video for you and share it may be today or tomorrow. Thanks for asking little difficult question.
Thanks
Tamara Mitrovic Follow the Steps to Write #Solved on TOP to close properly.
Tamara Mitrovic Using Unpivot option in PowerQuery. I will recommend you to go for PowerQuery if you got some basic knowledge of it.
