E1:E7右拉{=IF(ROW(A1)>COUNTIF($A:$A,INDEX($A:$A,SMALL(IF(MATCH($A$1:$A$12,$A:$A,)=ROW($1:$12),ROW($1:$12)),COLUMN(A1)))),"",INDEX($A:$A,SMALL(IF(MATCH($A$1:$A$12,$A:$A,)=ROW($1:$12),ROW($1:$12)),COLUMN(A1)))&ROW(A1))
另解 先下拉 再右拉 {=IF(ROW()>COUNTIF($A:$A,SUBSTITUTE(INDEX(E:E,99^(ROW()=1)),1,)),"",INDEX($A:$A,ROW()+COUNT(0/($D$1:D99<>"")))&ROW())
另解E1:G7{=IFERROR(INDEX($A:$A,SMALL(IF(MMULT(N(TRANSPOSE(IF(MATCH($A$1:$A$12,$A:$A,)=ROW($1:$12),ROW($1:$12)))<ROW($2:$13)),Z1:Z12+1)=COLUMN(A1),ROW($1:$12)),ROW(A1)))&ROW(A1),"")
M1先下拉 再右拉 {=IF(ROW()>COUNTIF($A:$A,INDEX(M:M,99^(ROW()=1))&""),"",INDEX($A:$A,COUNT(0/($L$1:L$99<>""))+ROW()))
輔助B1:B12=SUMPRODUCT(1/COUNTIF(A$1:A1,A$1:A1))
B列輔助I10:K16{=IFERROR(INDEX($A:$A,SMALL(IF($B$1:$B$12=COLUMN(A1),ROW($1:$12)),ROW(A1)))&ROW(A1),"")
留言
張貼留言