如何修改函数去重

janemark

区域E2:F3是用函数公式得来的，但是单元格F2中却有重复的值---橘子，如何修改函数去掉重复的值。

请看附件

leepoison

=GROUPBY(B2:B5,C2:C5,LAMBDA(x,ARRAYTOTEXT(UNIQUE(x))),0,0)

无关岁月

1. =LET(_a,UNIQUE(B2:C5),GROUPBY(INDEX(_a,,1),INDEX(_a,,2),ARRAYTOTEXT,,0))

复制代码

忙到飞起

=LET(a,UNIQUE(B2:B5),b,DROP(REDUCE(1,a,LAMBDA(x,y,VSTACK(x,TOROW(UNIQUE(FILTER(C2:C5,B2:B5=y)))))),1),IFNA(HSTACK(a,b),""))
写的老长了

你是那块小饼干

=GROUPBY(B2:B5,C2:C5,LAMBDA(_X,ARRAYTOTEXT(UNIQUE(_X))),0,0)

zhangcheng6688

学习一下字典
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
ReDim brr(1 To UBound(arr), 1 To 2)
For i = 2 To UBound(arr)
   If Not d.exists(arr(i, 2)) Then
      Set d(arr(i, 2)) = CreateObject("scripting.dictionary")
   End If
      d(arr(i, 2))(arr(i, 3)) = ""
Next
For Each kk In d.keys
   m = m + 1
   brr(m, 1) = kk
   For Each kk1 In d(kk).keys
      brr(m, 2) = brr(m, 2) & "," & kk1
   Next
   brr(m, 2) = Mid(brr(m, 2), 2)
Next
[e2].Resize(m, 2) = brr
End Sub

zzyglgl

学到了，谢谢！

hugeinfo

=DROP(REDUCE("",UNIQUE(B2:B5),LAMBDA(x,y,VSTACK(x,HSTACK(y,TEXTJOIN(",",,UNIQUE(FILTER(C2:C5,B2:B5=y))))))),1)



=GROUPBY(B2:B5,C2:C5,LAMBDA(a,TEXTJOIN(",",1,UNIQUE(a))),,0)

hxj119

1. =GROUPBY(B2:B5,C2:C5,LAMBDA(x,ARRAYTOTEXT(UNIQUE(x))),,0)

复制代码

如果没有GROUPBY函数，用REDUCE函数实现

1. =REDUCE(B1:C1,UNIQUE(B2:B5),LAMBDA(x,y,VSTACK(x,HSTACK(y,ARRAYTOTEXT(UNIQUE(FILTER(C2:C5,B2:B5=y)))))))

复制代码

wgreen

#在这里快速=TEXTJOIN(",",TRUE,UNIQUE(FILTER(C2:C5,B2:B5=E2),,FALSE))回复#