excel吧 关注：282,335贴子：1,559,906
- 12回复贴，共1页
不感兴趣
开通SVIP免广告
菠萝蜜
Option Explicit
Sub abc()
Dim a, b, i, j, k, t, d(1)
a = [a1].CurrentRegion.Offset(1).Resize(, 2).Value
For i = 0 To UBound(d)
Set d(i) = CreateObject("scripting.dictionary")
Next
For i = 1 To UBound(a) - 1
d(0)(a(i, 1)) = d(0)(a(i, 1)) & "、" & a(i, 2)
Next
For Each i In d(0).keys
t = Split(d(0)(i), "、")
For j = 1 To UBound(t)
d(1)(t(j)) = 1
Next
b = d(1).keys: d(1).RemoveAll
For j = 0 To UBound(b) - 1
For k = j + 1 To UBound(b)
If b(j) > b(k) Then
t = b(j): b(j) = b(k): b(k) = t
End If
Next
Next
d(0)(i) = Join(b, "、")
Next
[d2].Resize(d(0).Count, 2) = _
Application.Transpose(Array(d(0).keys, d(0).items))
End Sub
问题:合并对应品牌的全部产品,并去重
用这个公式可以得到对应品牌的全部产品名
=TEXTJOIN("、",1,IF(A2:A34=D2,B2:B34,""))
但内容没有去重
高阶版本就只需要用textsplit分列,UNIQUE去重,在textjoin合并就行了
我没有
我用老套分列去重套路
=TEXTJOIN("、",1,IF(MATCH(TRIM(MID(SUBSTITUTE(TEXTJOIN("、",1,IF($A$2:$A$34=D2,$B$2:$B$34,"")),"、",REPT(" ",200)),ROW($1:$50)*200-199,200)),TRIM(MID(SUBSTITUTE(TEXTJOIN("、",1,IF($A$2:$A$34=D2,$B$2:$B$34,"")),"、",REPT(" ",200)),ROW($1:$50)*200-199,200)),0)=ROW($1:$50),TRIM(MID(SUBSTITUTE(TEXTJOIN("、",1,IF($A$2:$A$34=D2,$B$2:$B$34,"")),"、",REPT(" ",200)),ROW($1:$50)*200-199,200)),""))
不感兴趣
开通SVIP免广告
