[發問] 找重複資料項

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

9^#

發表於 2014-4-6 12:05 | 只看該作者

回復 7# GBKEE

by the way,剛才試了,選擇紅色整列刪除不行,因為會把隱藏的列也刪除了,用依儲存格底色篩選出紅色再刪除就沒有問題了.

lmh

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

8^#

發表於 2014-4-6 11:47 | 只看該作者

回復 7# GBKEE
GBKEE,你總有令人興奮的答案給我,我試了你的方法:1速度極快(160000條,16秒).2不用過渡sheet(Temp),user不用頁間跳來跳去,既方便多多,而且不易出錯.3把重複資料分成2組,讓user可以選擇紅色儲存格整列刪除之,方便多多.
我原設計是找到重複列,全部刪除,要求user,再重新輸入一次"有關分公司有關日期(所有車間所有組)",因為出現重複資料,user一定至少分2次輸入資料的,那麼2次之間,user有可能改動"F:N"的資料,如果依重複資料出現先後分成2組,刪除紅色,那麼一定是后一次輸入的資料,刪得對不對得實際做做看,如果一定刪除第二次輸入(紅色),那麼又多了一個好處:4 user省卻重新輸入一次操作.
我知道dictionary也可以獲得唯一值,但具體做法不清楚,我一直用collection取唯一值,看了你的介紹,學習到取唯一值之外,還可以依重複值出現次序,分二祖顯示重複值,真是太感謝你了.

lmh

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

7^#

發表於 2014-4-6 07:17 | 只看該作者

回復 6# mhl9mhl9

Option Explicit
Sub Ex()
Dim D As Object, Rng(1 To 2) As Range, R As Range, Ar As String, T As Date
Set D = CreateObject("scripting.dictionary")
T = Time
With Sheets("資料庫")
.Rows.Hidden = False '取消存格格的隱藏
Set Rng(1) = .Range("B:E").SpecialCells(xlCellTypeConstants).Rows
Rng(1).Interior.ColorIndex = xlNone '取消圖樣顏色
For Each R In Rng(1)
Ar = Join(Application.Transpose(Application.Transpose(R.Value)), ",")
If D.exists(Ar) Then
D(Ar).Interior.Color = vbYellow '有重複圖樣顯示黃色
R.Interior.Color = vbRed '重複資料圖樣顯示紅色
If Rng(2) Is Nothing Then
Set Rng(2) = Union(.Rows(1), R, D(Ar))
Else
Set Rng(2) = Union(Rng(2), R, D(Ar))
End If
Else
Set D(Ar) = R
End If
Next
If Not Rng(2) Is Nothing Then
.Rows.Hidden = True
Rng(2).Rows.Hidden = False
.Cells(1).Activate
End If
MsgBox Application.Text(Time - T, "共費時SS秒")
End With
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

6^#

發表於 2014-4-6 04:40 | 只看該作者

回復 5# ML089
十分謝謝,雖然我最初提出的問題沒有用你的方法(文件已用VB改妥了,不想再多煩,特別是排序后能不能正常工作不知道)
但看了你的公式受到啓發,我把文件原來用自定義函數改為直接寫在儲存格里,用了3個過渡數據,(共6個公式,每個公式要向下copy160000格)
出乎我意料,速度竟然快了3倍多,原來7分鐘完成一個操作,現在102秒就完成,感覺上舒服了好多.但連續工作了7小時!
每次到這個網上看看,總會有收穫,謝謝你.

lmh

版主 Rank: 7 Rank: 7 Rank: 7

ML089

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2014-4-6 01:54 | 只看該作者

回復 3# mhl9mhl9

一、排序 B、C、D、E欄，適用於EXCEL2007板以上，EXCEL 2003只能排序3欄可以另外加輔助欄將B、C、D、E欄連接(F2=B2&C2&D2&E2)成一欄來排序
二、查詢重覆公式
G2 =IF((B2&C2&D2&E2=B1&C1&D1&E1)+(B2&C2&D2&E2=B3&C3&D3&E3),"重複","")
下拉

若你會用VBA當然會更加便利，我只會簡單VBA沒有辦法給你提供意見。

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

4^#

發表於 2014-4-5 21:35 | 只看該作者

回復 2# ML089
原來160000條資料,因為文件超過1M不能上傳,所以留下50000條,

lmh

帖子: 214
主題: 74
精華: 0
積分: 296
點名: 0
作業系統: win7
軟體版本: office2007
閱讀權限: 20
性別: 男
來自: hk
註冊時間: 2013-6-17
最後登錄: 2018-11-3

3^#

發表於 2014-4-5 21:33 | 只看該作者

回復 2# ML089

Book100.rar (979.01 KB)
謝謝你,你的方法簡單易明,我留下copy
因為等不及網上答覆,我自己寫了一個,見附件,速度還滿意.
我再講一講自己的要求:
我的文件里資料庫是用戶自己輸入,我怕他們重複輸入資料, 所以要做一個"檢查重複資料"的功能.
資料庫共有14欄,只有"B,C,D,E"不能完全重複,其他欄位因為可以改動,所以不管它了,我只管"同分公司同日期同車間同組"不能重複
我把BCDE這4欄相加,用2次collection能取唯一值的方法,在"Temp頁"show出重複的資料.
你是不是有時間看看能不能用你的方法,多了個排序,我的資料排序結果也不知道可不可以用你的公式.
再次謝謝

lmh

版主 Rank: 7 Rank: 7 Rank: 7

ML089

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2014-4-5 17:22 | 只看該作者

回復 1# mhl9mhl9

陣列公式"=countif($A$1:$A$160000,A1:A160000)" 一定要跑很久

建議先將A欄排序
B1公式
=IF(COUNT(0/(OFFSET(A1,-1,)=A1),0/(A2=A1)),"重複","")
下拉

若第一列為表頭，資料由第二列開始，公式更簡單
B2公式
=IF((A2=A1)+(A2=A3),"重複","")
下拉

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式