表格转换问题

jylai

请老师帮忙看一看
从 A跟B 得到  D跟E

hugeinfo

=REDUCE({"区域","名称"},UNIQUE(A2:A8),LAMBDA(x,y,VSTACK(x,IFERROR(VSTACK(y,HSTACK(IF(SEQUENCE(COUNTIF(A2:A8,y)),""),FILTER(B2:B8,A2:A8=y))),""))))

mykitami

=DROP(REDUCE("",UNIQUE(FILTER(A2:A16,A2:A16<>"")),LAMBDA(x,y,LET(a,FILTER(B2:B16,A2:A16=y),VSTACK(x,HSTACK(EXPAND(y,ROWS(a)+1,,""),VSTACK("",a)))))),1)

剑指E

1. =REDUCE(A1:B1,A2:A8,LAMBDA(x,y,LET(z,HSTACK("",OFFSET(y,,1)),VSTACK(x,IF(y<>OFFSET(y,-1,),VSTACK(HSTACK(y,""),z),z)))))

复制代码

jylai

各层老师都可以达到要求，真心感谢三位老师

quqiyuan

vba参与一下，同一个关键字可以放到一起，供参考。。。

quqiyuan

代码如下。。
Sub test()
    arr = Sheet1.[a1].CurrentRegion
    Set d = CreateObject("scripting.dictionary")
    Dim brr(1 To 10000, 1 To 2)
    For i = 2 To UBound(arr)
        If Not d.exists(arr(i, 1)) Then Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
        d(arr(i, 1))(i) = Application.Index(arr, i)
    Next
    For Each k In d.keys
        n = n + 1
        brr(n, 1) = k
        For Each kk In d(k).keys
            n = n + 1
            brr(n, 2) = d(k)(kk)(2)
        Next
    Next
    Sheet1.[h1].Resize(n, 2) = brr
    Beep
End Sub

华尔街操盘手

1. =DROP(REDUCE("",UNIQUE(A2:A8),LAMBDA(x,y,IFERROR(VSTACK(x,VSTACK(HSTACK(y,""),HSTACK("",FILTER(B:B,A:A=y)))),""))),1)

复制代码

你是那块小饼干

1. =REDUCE({"区域","名称"},UNIQUE(A2:A8),LAMBDA(X,Y,VSTACK(X,IFNA(HSTACK(Y,VSTACK("",FILTER(B2:B8,Y=A2:A8,""))),""))))

复制代码