=LET(rngAl,B2:H13, xKeys,K2:K5, xReps,L2:P5, xTC,TOCOL(rngAl), ReVe,MAP(SEQUENCE(ROWS(xTC)),LAMBDA(iseq,
LET(xVal,INDEX(xTC,iseq), IF(xVal="","",LET(RwIdx,MATCH(xVal,xKeys,0), xCns,SUM(--(TAKE(xTC,iseq)=xVal)), INDEX(xReps,RwIdx,xCns)))))),
WRAPROWS(ReVe,COLUMNS(rngAl)))

​

파워쿼리로 만들어 봅니다.

=LET(DT,B2:H13, RF, K2:P5, DC, TOCOL(DT), SQ, SEQUENCE(ROWS(DC)),
DI, MAP(SQ, LAMBDA(m, SUM((TAKE(DC,m)=INDEX(DC,m))*1))),
DR, MAP(SQ, LAMBDA(m, INDEX(RF, MATCH(INDEX(DC,m),TAKE(RF,,1),0),INDEX(DI,m)+1))),
IFERROR(WRAPROWS(DR,COLUMNS(DT)),""))

일렬로 세우고, 순번을 채번하고, Index로 좌표찍기

=LET(Area,$B$2:$H$13,vD,$K$2:$P$5, Data,TOCOL(Area&""),
mData, MAP(SEQUENCE(ROWS(Data)), Data, LAMBDA(n,m, IF(m="","", VLOOKUP(m, vD,1+COUNT(1/(TAKE(Data,n)=m)),0)))),
WRAPROWS(mData, COLUMNS(Area)))

​