无重复列出来的公式怎么做，求指教

sailor_88

无重复列出来的公式怎么做，求指教

mykitami

=UNIQUE(B2:B30)

ww87725244

=OFFSET($B$1,SMALL(IF(MATCH($B$2:$B$30,$B$1:$B$30,)=ROW($B$2:$B$30),ROW($B$2:$B$30),""),ROW(A1))-1,)

sailor_88

ww87725244 发表于 2024-10-6 17:26
=OFFSET($B$1,SMALL(IF(MATCH($B$2:$B$30,$B$1:$B$30,)=ROW($B$2:$B$30),ROW($B$2:$B$30),""),ROW(A1))-1,)

2010版，如果行数要无限大，公式怎么改进？

cnlikai

=LOOKUP(1,0/FREQUENCY(1,ISNA(MATCH(B:B,$J$1:$J1,))*(B:B<>"")),B:B)&""

忙到飞起

=UNIQUE(B2:B30)v

quqiyuan

=INDEX(B:B,MATCH(0,COUNTIF($J$1:J1,$B$1:B1000),))&""
写了1000行，根据需要可以改

quqiyuan

直接用列号，估计有点卡。。。
=INDEX(B:B,MATCH(0,COUNTIF($J$1:J1,IF(B:B<>"",B:B)),))&""

zhangcheng6688

学习一下字典
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   d(arr(i, 2)) = ""
Next
[j1].CurrentRegion.Offset(1).ClearContents
[j2].Resize(d.Count) = Application.WorksheetFunction.Transpose(d.keys)
End Sub