So sánh trùng và không trùng trên 2 cột. (1 người xem)

[viendo]

Tôi làm file có các cột như sau :

1. Cột A : Dữ liệu so sánh 1
2. Cột B : Dữ liệu so sánh 2
3. Cột C : Lọc dữ liệu có trong cột A mà không có trong cột B
4. Cột D : Lọc dữ liệu có trong cột B mà không có trong cột A
5. Cột E : Lọc dữ liệu trùng cột A và B
   
   Sau khi thử dữ liệu 300 dòng thì nó đã "lết bánh" rồi, trong khi công việc đang cần so sánh số liệu khoảng 50.000 dòng.
   Nhờ các Anh Chị xem hướng dẫn sửa Name và công thức dùm, làm bằng BVA cũng tốt, miễn sao nó chạy lẹ lẹ 1 tí. Thanks

 

[SA_DQ]

Bạn Thử Với 2 Cái Ni Xem Tốc Độ Ra Răng?

Option Explicit
 Dim lRow As Long, jZ As Long
 Dim lRow0 As Long, jW As Long
  
Sub FilterAll()
 Dim SChu As String
 Dim XH As String
 
 XH = Chr(13) & Chr(10)
 lRow = Range("A65432").End(xlUp).Row
 lRow0 = Range("B65432").End(xlUp).Row
 SChu = "A: Co O Cot 'A', Khong Co O Cot 'B'" & XH
 SChu = SChu & "B: Co O Cot 'B', Khong Co O Cot 'A'"
 SChu = SChu & XH & "C: Co O Ca 2 Cot"
 SChu = InputBox(SChu, "GPE - SaDQ")
 SChu = UCase$(Left(SChu, 1))
 Application.ScreenUpdating = False
 If SChu = "A" Or SChu = "B" Then
    TongHop SChu
 ElseIf SChu = "C" Then
    Dim tRow As Long
    If lRow > lRow0 Then tRow = lRow Else tRow = lRow0
    Range("E3:C" & tRow).ClearContents
    For jZ = 3 To lRow
        For jW = 3 To lRow0
            If Cells(jZ, 1) = Cells(jW, 2) Then _
                Range("E" & Range("E65432").End(xlUp).Row + 1) = Cells(jZ, 1)
        Next jW
    Next jZ
 Else
    XH = "BAN CAN CHON 1 TRONG 3 FUONG AN!" & XH
    MsgBox XH & "           BYE!", , "GPE - SaDQ"
 End If
End Sub

Sub TongHop(MainCol As String)
 Dim Cot1 As Long, Cot2 As Long
 Dim StrC1 As String, StrC2 As String
 
 Select Case UCase$(MainCol)
 Case "A"
    Cot1 = lRow:        Cot2 = lRow0
    StrC1 = "C":        StrC2 = "C65432"
    Range("C3:C" & lRow).ClearContents
 Case "B"
    Cot1 = lRow0:        Cot2 = lRow
    StrC1 = "D":        StrC2 = "D65432"
    Range("D3:d" & lRow0).ClearContents
 End Select
 For jZ = 3 To Cot1
    For jW = 3 To Cot2
        If Cells(jZ, 1) = Cells(jW, 2) Then _
            Exit For
    Next jW
    If jW > Cot2 Then _
        Range(StrC1 & Range(StrC2).End(xlUp).Row + 1) = Cells(jZ, 1)
 Next jZ
End Sub

 

[feelingyes]

bạn có thê dùng công thức mảng
={if(Or(phần tử cột A = cột B),"trùng","ko trùng")}
Bạn làm tương tự cho cột B
Dùng Filter, lọc và paste sang một vùng mới.
Cái này tôi đã dùng rồi (Công ty tôi có danh sách gốc gần 200 người, danh sách tham gia cứu thương lần 1, .........tạo danh sách học cho những người chưa tham gia lần 1 sẽ tham gia lần 2 tôi cũng dùng cách này.

Bạn có thể tham khảo ở bài viết, trong đó có những cách của AnhTuan 1006 cũng rất hay mà chưa cần phải dùng đến VBA

http://www.giaiphapexcel.com/forum/showthread.php?t=8170

 

[viendo]

Sư phụ SA_DQ ơi tôi làm chưa được, phải nhờ các sư phụ chỉ tiếp và đừng la nhé vì chưa đủ hiểu cái code này và làm sao cho nó chạy, đang bị vướng thế này đây :

1. Chép thử code 1 và code 2 lần lượt vào vùng Immediate để chạy thử nó báo lỗi là Invalid in Immediate pane.
2. Chép code 1 lần lượt vào Module1, Thisworkkbbookk và Sheet1. Khi nhấn chạy nó đều báo lỗi là Sub or Function not defined. Ngoài ra nó tô vàng dòng Sub FillterAll() và tô đen Tonghop ở dòng TongHop Schu.
3. Chép code 2 lần lượt vào Module1, Thisworkkbbookk và Sheet1. Khi nhấn chạy nó hiện bảng Macro mà chẳng có cái Macro nào để chọn.
   
   Phiền các sư phụ chỉ giáo tiếp dùm. Thanks

 

[SA_DQ]

phải nhờ các sư phụ chỉ tiếp vì chưa đủ hiểu cái code này và làm sao cho nó chạy, đang bị vướng. Phiền các sư phụ chỉ giáo tiếp dùm.

Bạn nhấn tổ hợp fím {ALT} +{F11} để mở CS VBE
Và chép cả 2 macro vô module 1
Nói thêm: Macro TongHop được gọi bỡi macro còn lại & phải được nhận 1 đối số (biến) thích hợp 'A'/'B')
Như vậy bạn cần chạy cái macro còn lại kia (tên nó là FilterAll)
Khi đó 1 hộp thoại sẽ hỏi bạn cần chép vố cột nào tương ứng 'C', 'D' hay 'E'
Nếu bạn nạp biến 'A' hay 'B' thì macro này sẽ gọi macro còn lại (TongHop) truyền cho nó đối số tương ứng để nó chạy ra kết quả (KQ sẽ thể hiện trên cột 'C' hay 'D' tương ứng)
Nếu bạn nhập phương án 'C' thì macro FilterAll sẽ tiếp tục cho đến khi xong chuyện lọc ~ giá trị trùng trong 2 cột 'A' & 'B' ghi => 'E'
Bổ sung:
Nếu bạn muốn trường hợp 'C' nhanh hơn 1 tẹo nữa, thì trong macro FilterAll, tại dòng lệnh

If Cells(jZ, 1) = Cells(jW, 2) Then _ 
                Range("E" & Range("E65432").End(xlUp).Row + 1) = Cells(jZ, 1)

Nên sửa lại là (Chú í: tôi đã bỏ dấu gạch dưới dùng nối 2 dòng lệnh)

    If Cells(jZ, 1) = Cells(jW, 2) Then 
                Range("E" & Range("E65432").End(xlUp).Row + 1) = Cells(jZ, 1) 
                Exit For
    End If

 

[viendo]

bạn có thê dùng công thức mảng
={if(Or(phần tử cột A = cột B),"trùng","ko trùng")}
Bạn làm tương tự cho cột B
Dùng Filter, lọc và paste sang một vùng mới.
Cái này tôi đã dùng rồi (Công ty tôi có danh sách gốc gần 200 người, danh sách tham gia cứu thương lần 1, .........tạo danh sách học cho những người chưa tham gia lần 1 sẽ tham gia lần 2 tôi cũng dùng cách này.
Bạn có thể tham khảo ở bài viết, trong đó có những cách của AnhTuan 1006 cũng rất hay mà chưa cần phải dùng đến VBA
http://www.giaiphapexcel.com/forum/showthread.php?t=8170

Cám ơn bạn nhiều, tôi đã tham khảo cách làm trên rồi, file tôi làm cũng đa phần là học từ các file của Anhtuan, trường hợp hiện tại của tôi là phải xử lý số liệu rất lớn và phải làm khoảng 4 lần (mỗi lần làm 1 quí khoảng 40 đến 50 ngàn dòng) rồi mới tổng hợp nên nó chạy không nổi luôn.
Ý tôi làm file này để đối chiếu sai sót của số lượng kiểm kê thực tế và số liệu trong chương trình xuất ra có chênh lệch. Một mặt phiền các sư phụ giúp file xử lý, một mặt tôi đang đề nghị chỉ kiểm tra trong từng tháng thì số liệu còn khoảng mười mấy ngàn dòng thôi.

To Sư phụ SA_DQ : để làm theo sư phụ chỉ, có gì sẽ phiền tiếp nhé, do công lực yếu kém quá nên tiếp thu cũng kém luôn hichic..

 

[viendo]

Đã làm theo hướng dẫn của Sư phụ và chạy rất ok với dữ liệu <1.000 dòng. Cám ơn rất nhiều.
Cho chạy thử Cột A 10.000 dòng và cột B 5.000 dòng nó chạy gần 20' Sư phụ ơi. (với cấu hình máy sau Processor: Intel(R) Pentium(R) 4 CPU 1.50GHz. Memory: 512MB RAM)
Có cách nào để cải thiện tiếp về tốc độ xử lý phiền các Sư phụ chỉ giáo tiếp. Thanks

 

[SA_DQ]

Đã chạy rất ok với dữ liệu; Chạy thử Cột A 10.000 dòng và cột B 5.000 dòng nó chạy gần 20' Có cách nào để cải thiện tiếp về tốc độ xử lý phiền các Sư phụ chỉ giáo tiếp.

1*/ Hãy cho biết t1, t2 & t3 tương ứng là bao nhiêu; Để còn định hướng cải thiện í mà!

2*/ Dữ liệu của bạn có thể cho phép sắp xếp hay không?
Nếu trong trường hợp được phép, thì thử trước khi chạy macro, cho xếp 2 cột này lại 1 cách riêng rẽ xem tình hình có cải thiện hơn không?

 

[viendo]

1*/ Hãy cho biết t1, t2 & t3 tương ứng là bao nhiêu; Để còn định hướng cải thiện í mà!

2*/ Dữ liệu của bạn có thể cho phép sắp xếp hay không?
Nếu trong trường hợp được phép, thì thử trước khi chạy macro, cho xếp 2 cột này lại 1 cách riêng rẽ xem tình hình có cải thiện hơn không?

1. Dữ liệu hàng tháng dao động từ 10.000 đến cao nhất là 15.000 dòng.
2. Dữ liệu này có thể sắp xếp, sort đều được vì mục đích là tìm ra các mã bị chênh lệch giữa 2 cột mà. Dữ liệu so sánh 2 cột thế này đây :
   .....
   SJL436MDFQ0030
   SJL436MDFQ0025
   HUL773ZABJ0006
   HUL773ZABJ0010
   HUL773ZABJ0004
   ADXN001EABB0019
   ADXN001EABB0020
   AEJL064ZAEJ0006
   AEJL064ZAEJ0007
   .....

Hy vọng Sư phụ tìm ra giải pháp giúp. Thanks

 

[SA_DQ]

Chúng ta thử từng phần việc, vậy!

Bạn vô CS VBE như trên đã nêu; Vô menu Insert & thêm 1 module nữa (tạm là module2) Và chép 2 macro này & chạy thử, so sánh với macro FilterAll -> TongHop (A) & báo kết quả nha!
Chú í: Mình mới tách & thử với trường hợp hình thành KQ trên cột 'C' thôi nha! Hai trường hợp còn lại sẽ tiếp, một khi cái này có xu hướng không tồi!

Option Explicit
Dim lRow As Long, jZ As Long
Dim lRowB As Long, jW As Long
Dim Timer_ As Double

Sub SearchColumnC()
 Dim lTemp As Long
 
 Timer_ = Timer
 lRow = Range("A65432").End(xlUp).Row
 lRowB = Range("b65432").End(xlUp).Row
 Application.ScreenUpdating = False
 Sort1Col Range("A2:A" & lRow), Range("A3")
 Sort1Col Range("B2:B" & lRow), Range("B3")
 Range("C2") = "List1<>List2"
 Range("C3:C" & lRowB).ClearContents
 lTemp = 3
 For jZ = 3 To lRow
    For jW = lTemp To lRowB
        If Cells(jZ, 1) = Cells(jW, 2) Then
            lTemp = jW:         Exit For
        End If
    Next jW
    If jW > lRowB Then _
        Range("C" & Range("C65432").End(xlUp).Row + 1) = Cells(jZ, 1)
 Next jZ
 MsgBox Str(Timer - Timer_)
End Sub

Sub Sort1Col(Rng As Range, Clls As Range)
  Rng.Sort Key1:=Clls, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

 

[minhlev]

To bác Sa_DQ: Em nhận thấy trong code của bác có 2 vòng lặp For...Next.

....
    For jZ = 3 To lRow
          For jW = 3 To lRow0
                If Cells(jZ, 1) = Cells(jW, 2) Then _
                Range("E" & Range("E65432").End(xlUp).Row + 1) = Cells(jZ, 1)
          Next jW
    Next jZ

Theo em, nếu thay cái vòng lặp For...Next con bằng 1 cái CountIf thì tốc độ có thể cải thiện hơn ạ.

....
    For jZ = 3 To lRow
        If WorksheetFunction.CountIf(Range("B3:B" & lRow0), Cells(jZ, 1)) > 0 Then _
                Range("E" & Range("E65432").End(xlUp).Row + 1) = Cells(jZ, 1)
    Next jZ

 

[viendo]

Bạn vô CS VBE như trên đã nêu; Vô menu Insert & thêm 1 module nữa (tạm là module2) Và chép 2 macro này & chạy thử, so sánh với macro FilterAll -> TongHop (A) & báo kết quả nha!
Chú í: Mình mới tách & thử với trường hợp hình thành KQ trên cột 'C' thôi nha! Hai trường hợp còn lại sẽ tiếp, một khi cái này có xu hướng không tồi!

Sub Sort1Col(Rng As Range, Clls As Range)
Rng.Sort Key1:=Clls, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Nó báo lỗi không chạy Sư phụ ơi, khi ở cửa sổ VBE nhấn run thì nó báo lỗi Compile error : Variable not defined. Khi ở cửa sổ excel chạy thì nó báo lỗi Can't execute code in break mode.
Cả 2 trường hợp trên đều báo lỗi ở Sub Sort1Col(). Sư phụ xem lại dùm với. Thanks
Bây giờ chạy macro ở module 1 nó cũng báo lỗi luôn Can't execute code in break mode.

 

[phamduylong]

Tôi làm file có các cột như sau :

1. Cột A : Dữ liệu so sánh 1
2. Cột B : Dữ liệu so sánh 2
3. Cột C : Lọc dữ liệu có trong cột A mà không có trong cột B
4. Cột D : Lọc dữ liệu có trong cột B mà không có trong cột A
5. Cột E : Lọc dữ liệu trùng cột A và B

Bạn thử Sub SoSanh này:

Sub SoSanh()
Dim List1 As Range, List2 As Range, List12 As Range
Sheets("Check").Select
Range(Cells(3, 3), Cells(65536, 5)).ClearContents
rc = Cells(3, 1).End(xlDown).Row
Set List1 = Range(Cells(3, 1), Cells(rc, 1))
Set List2 = Range(Cells(3, 2), Cells(rc, 2))
Set List12 = Range(Cells(3, 5), Cells(rc, 5))
r1 = 3
r2 = 3
r12 = 3
For r = 3 To rc
[COLOR=blue]  'So sanh list1 voi list2[/COLOR]
  kiemtra = Application.WorksheetFunction.CountIf(List2, Cells(r, 1))
  If kiemtra = 0 Then 'list1<>list2
    Cells(r1, 3) = Cells(r, 1)
    r1 = r1 + 1
  Else                  [COLOR=blue] 'list1=list2
[/COLOR]    kiemtra = Application.WorksheetFunction.CountIf(List12, Cells(r, 1))
    If kiemtra = 0 Then
      Cells(r12, 5) = Cells(r, 1)
      r12 = r12 + 1
    End If
  End If
  
 [COLOR=blue] 'So sanh list1 voi list2[/COLOR]
  kiemtra = Application.WorksheetFunction.CountIf(List1, Cells(r, 2))
  If kiemtra = 0 Then 'list2<>list1
    Cells(r2, 4) = Cells(r, 2)
    r2 = r2 + 1
  Else                  [COLOR=blue] 'list1=list2
[/COLOR]    kiemtra = Application.WorksheetFunction.CountIf(List12, Cells(r, 2))
    If kiemtra = 0 Then
      Cells(r12, 5) = Cells(r, 2)
      r12 = r12 + 1
    End If
  End If
Next
End Sub

 

[SA_DQ]

Macro chinh & Macro phụ.

Một khi báo lỗi thì nên thoát ra đâu đó, như windows chẵng hạn, hút điếu thuốc/làm ly cà phê mới quay lại excel, bình tâm mà chạy tiếp;
Chú í các macro cần được cung cấp biến, ta không thể dùng {F5} để chạy trực tiếp nó được;
Bạn nhớ chép tất thẩy các dòng lệnh, kể các các biến khai báo trên cả các macro; đó là ~ biến dùng chung;
Chép xong thử vô menu Debug & biên dịch xem thử, VBA có cự nự gì về biến hay không.
Trước khi chạy macro bằng fím {F5} phải nhớ mở sheets chứa dữ liệu cho excel nó thấy, để nó tìm, chứ không nó trách chúng ta thờ ơ chuyện này!!
Nếu bạn biết làm nút lệnh trên trang tính thì thực thi đi; để đỡ tốn thời gian!

NHỜ CÁC MOD/SMOD XÓA BÀI NÀY SAU 2 GIỜ, XIN CẢM ƠN!​

 

[dmtdmtbb]

Xem thử cái này coi có nhanh hơn chút nào không !
Kiểm tra dữ liệu với 10.000 dòng sẽ thấy kết quả.

 

[Mr Okebab]

To bác Sa_DQ: Em nhận thấy trong code của bác có 2 vòng lặp For...Next.

Theo em, nếu thay cái vòng lặp For...Next con bằng 1 cái CountIf thì tốc độ có thể cải thiện hơn ạ.

Dùng Countif quả thật nhanh hơn For (đã có bài về vấn đề này), tuy nhiên hàm MATCH sẽ nhanh hơn nhiều.

Đơn giản bởi Countif luôn luôn phải quét hết mảng, còn MATCH thì không, đặc biệt là với mảng được sắp xếp.

Thân!

 

[viendo]

Xin cám ơn các sư phụ đã tận tình.
Test 3 file của 3 Sư phụ luôn trên máy Intel(R) Pentium(R) 4 CPU 3.00GHz (2CPUs), Memory 256MB RAM với dữ liệu là mỗi list 15.000 dòng :

1. SA_DQ : Chạy code ở module1 cho cột C hơn 05' thế là đệ tử cho End luôn, Chạy code ở module2 cho cột C chỉ có 25". Vậy sư phụ cải tiến tiếp cho cột D, E nữa nhé, vì đôi khi cũng chỉ cần lấy kết quả 1 cột.
2. Thầy Long : Chạy ra 3 cột luôn mất 03'09".
3. Dmtdmtbb : Chạy ra 3 cột luôn mất 08'20".
   
   Trong đó có 1 điểm khác là ở kết quả cột E của Thầy Long và Dmtdmtbb :
   1. File Thầy Long lấy danh sách duy nhất không có trùng.
   2. Dmtdmtbb lấy danh sách có trùng luôn, vì vậy mà chạy lâu hơn.
      Không lường trước tình huống này nhưng đều sử dụng được cả 2 file này vì 2 list cột A và B bản thân không có trùng.
      
      Vậy là đệ tử lấy luôn 3 file này nhé, phiền sư phụ SD_QD làm tiếp cột D, E nữa nhé. Một lần nữa xin cám ơn sự tận tình giúp đỡ của các Thầy, các sư phụ.

 

[SA_DQ]

Dùng Countif quả thật nhanh hơn For (đã có bài về vấn đề này), tuy nhiên hàm MATCH sẽ nhanh hơn nhiều. Đơn giản bởi Countif luôn luôn phải quét hết mảng, còn MATCH thì không, đặc biệt là với mảng được sắp xếp. Thân!

Đúng BAB tuyệt chiêu!
Mình thử 3 phương án với cột 'C' khi dữ liệu cột 'A' = 1.000 dòng; cột 'B' = 400 dòng
Mcr của mình mất: 1.1094'' (làm tròn)
Theo hàm COUNTIF(): 0.3438''
Theo hướng dẫn của BAB: 0.1875''
Mà dùng 2 hàm này thấy nhẹ hẵn macro đi rất nhiều
Sau đây xin giới thiệu đến VienDo, nha:

Option Explicit
Public Timer_ As Double
Dim lRow As Long, jZ As Long
Dim lRowB As Long, jW As Long
Sub SearchColumnF()
 Dim lTemp As Long
 
 Timer_ = Timer
 lRow = Range("A65432").End(xlUp).Row
 lRowB = Range("b65432").End(xlUp).Row
 Application.ScreenUpdating = False
 Sort1Col Range("A2:A" & lRow), Range("A3")
 Sort1Col Range("B2:B" & lRow), Range("B3")
 Range("C2") = "List1<>List2"
 Range("C3:C" & (lRowB + lRow)).ClearContents
 lTemp = 3
 For jZ = 3 To lRow
'    If WorksheetFunction.CountIf(Range("B3:B" & lRowB), Cells(jZ, 1)) = 0 Then '
    If WorksheetFunction.Match(Cells(jZ, 1), Range("B3:B" & lRowB)) = lRowB - 2 Then
        Range("C" & Range("C65432").End(xlUp).Row + 1) = Cells(jZ, 1)
    End If
 Next jZ
 Range("F7") = Timer - Timer_
End Sub

 Public Sub Sort1Col(Rng As Range, Clls As Range)
  Rng.Sort Key1:=Clls, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

VienDo chú í:
Mình đã khai báo 1 biến toàn cục Timer_ ; Coi chừng báo lỗi khi biên dịch nha;
Cũng như vậy với macro Sort1Cot ;
Có thể xóa macro Sort1Cot & biến Timer_ cũ đi; Một khi đã chép toàn bộ 2 macro này vô module thứ 3 của CS VBE

 

[dmtdmtbb]

Trong đó có 1 điểm khác là ở kết quả cột E của Thầy Long và Dmtdmtbb :

1.File Thầy Long lấy danh sách duy nhất không có trùng.
2.Dmtdmtbb lấy danh sách có trùng luôn, vì vậy mà chạy lâu hơn.
Không lường trước tình huống này nhưng đều sử dụng được cả 2 file này vì 2 list cột A và B bản thân không có trùng.

Để lấy kết quả cột E có 3 cách :
C1 : so sánh A và B tìm những phần tử trùng nhau cho qua cột E,lâu hơn nhưng độc lập.
C2 : sau khi lọc được A<>B, dựa vào cột này loại bỏ những phần tử vừa lọc => (A=B),nhanh hơn nhiều.
C3 : Trong khi lọc A<>B thì cho qua E những phần tử A=B, bỏ qua bước lọc A=B, cực nhanh.

 

[SA_DQ]

Để lấy kết quả cột E có 3 cách :
C1 : so sánh A và B tìm những phần tử trùng nhau cho qua cột E,lâu hơn nhưng độc lập.
C2 : sau khi lọc được A<>B, dựa vào cột này loại bỏ những phần tử vừa lọc => (A=B),nhanh hơn nhiều.
C3 : Trong khi lọc A<>B thì cho qua E những phần tử A=B, bỏ qua bước lọc A=B, cực nhanh.

Nhưng chúng ta chưa hỏi tác gia Topic cho biết đặt thù của dữ liệu tại cột 'A' & 'B' NTN (!)
Nếu 'A' gấp 12 lần 'B' hay ngược lại Hoặc thường chỉ tìm thấy 1/10 trên 'C' thì cách giải có thể # so với chương trình chuẩn.
Tiện đây, cho hỏi về dữ liệu của VD luôn, hàng tháng thường sẽ NTN?