關(guān)于剔除重復(fù)

pureshadow

原帖由 Jonathan.K 于 2008-3-27 10:03 發(fā)表
Sub test()
    Dim rngData As Variant
    Dim i As Long
    Dim oDic As Object
   
    Application.ScreenUpdating = False
    Set oDic = CreateObject("Scripting.Dictionary")
    With Sheet1
  ...

現(xiàn)在才看明白里面的意思
這恐怕是運(yùn)行速度最快的一種了[:31]

洋五月

呵呵，妖都才明白，我更看不懂了[:33]

wait→alone

數(shù)據(jù)量大的話用sql吧
Sub cx()
    Dim cnn As New ADODB.Connection
    Dim rds As New ADODB.Recordset
    Dim i As Integer
    Dim sql As String, sfilename As String, cnnstr As String
    sfilename = ThisWorkbook.FullName
    cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & sfilename
    sql = "select distinct * from [sheet1$]  ;"
    cnn.Open cnnstr
    rds.Open sql, cnn, adOpenKeyset, adLockOptimistic
    Sheets("sheet1").Range("b2").CopyFromRecordset rds
    rds.Close: cnn.Close
    Set rds = Nothing: Set cnn = Nothing
End Sub

pureshadow

SQ的速度也比不上字典，字典是VBA里比數(shù)組更快上十倍的

wait→alone

學(xué)習(xí)了
找時(shí)間測(cè)試下

pureshadow

函數(shù)是有無(wú)數(shù)種解決方法的，先來(lái)一種較易理解的：

例如原數(shù)據(jù)在A2：A11，要剔除重復(fù)。

核心在于把不重復(fù)的按序排好，重復(fù)的T到一邊去：
SMALL(IF(MATCH($A$2: $A$11,$A$2: $A$11,)=ROW($A$1: $A$10),ROW($A$1 ...
pureshadow 發(fā)表于 2008-3-26 16:17

回頭看看以前自己寫(xiě)的函數(shù)，真不是一般的爛
簡(jiǎn)化一下中間部分：
small(if(match($a$2: $a$11,$a$2: $a$11,)=row($1: $10),row($1: $10),11),row(a1))

wyingnan4587

還是數(shù)據(jù)透視最快了

szhtct

老黃歷~~~

douhao

支持，用數(shù)據(jù)透視表操作快捷

sl261011

SEE