Hỗ trợ lọc trùng bằng phương pháp mảng

[Tình nghĩa giang hồ]

Chào anh chị, em có trường hợp này nhờ anh chị hỗ trợ giúp.
Dữ liệu gốc của em là cột A, và cột B
Dữ liệu kế toán cung cấp là cột F, cột G, cột H
Vấn đề của em chỉ đơn giản là lọc trùng

Kết quả của em là sheet Tontai và sheet Khongtontai

Nhưng mà cách làm Condition formating, và Vlookup không hiệu quả.
Do dữ liệu khách hàng của bên em là gần 100 ngàn mã, cộng với các company code: 1000,1001,1002,1003,1004,2000,3000.....
Dữ liệu nó quá nhiều.
Em có đọc các code lọc trùng của GPE, nhưng đa số cách viết thông thường, và cũng không đúng ý.
Em chạy sợ bị chậm hoặc treo máy giống công thức.
Nhờ anh chị hỗ trợ giúp em trường hợp này bằng phương pháp mãng, hoặc dictionary........cách nào mà dữ liệu nhiều vẫn hoạt động nhanh được là được nhé anh chị
Em cảm ơn anh chị nhiều.
(File này làm thường xuyên, và gửi cho cả team công nợ bên em làm)

 

[BuiQuangThuan]

Thấy dữ liệu nhiều cái không trùng lắm mà sao trong sheet Khongtontai lại có 2 dòng nhỉ

 

[ptm0412]

Thấy dữ liệu nhiều cái không trùng lắm mà sao trong sheet Khongtontai lại có 2 dòng nhỉ

Dữ liệu mẫu thì có kết quả mẫu. Nếu kết quả liệt kê được hết thì đâu cần nhờ?

 

[Ronaldinho7]

Chào anh chị, em có trường hợp này nhờ anh chị hỗ trợ giúp.
Dữ liệu gốc của em là cột A, và cột B
Dữ liệu kế toán cung cấp là cột F, cột G, cột H
Vấn đề của em chỉ đơn giản là lọc trùng

Kết quả của em là sheet Tontai và sheet Khongtontai

Nhưng mà cách làm Condition formating, và Vlookup không hiệu quả.
Do dữ liệu khách hàng của bên em là gần 100 ngàn mã, cộng với các company code: 1000,1001,1002,1003,1004,2000,3000.....
Dữ liệu nó quá nhiều.
Em có đọc các code lọc trùng của GPE, nhưng đa số cách viết thông thường, và cũng không đúng ý.
Em chạy sợ bị chậm hoặc treo máy giống công thức.
Nhờ anh chị hỗ trợ giúp em trường hợp này bằng phương pháp mãng, hoặc dictionary........cách nào mà dữ liệu nhiều vẫn hoạt động nhanh được là được nhé anh chị
Em cảm ơn anh chị nhiều.
(File này làm thường xuyên, và gửi cho cả team công nợ bên em làm)

Không biết phải thế này không?
Kiểm tra lại thử xem nhé!

Option Explicit
Sub GPE()
    Dim Dic As Object, Key$, Arr(), Res(1 To 1000000, 1 To 3)
    Dim Lr1&, Arr1(), i&, k&, m&, Lr&, Res1(1 To 1000000, 1 To 3)
    Set Dic = CreateObject("Scripting.Dictionary")
    With Sheets("Sheet1")
        Lr = .Range("A" & Rows.Count).End(xlUp).Row
        Lr1 = .Range("F" & Rows.Count).End(xlUp).Row
        Arr = .Range("A2:B" & Lr).Value
        Arr1 = .Range("F2:H" & Lr).Value
        For i = 1 To UBound(Arr)
            If Arr(i, 1) <> "" And Arr(i, 2) <> "" Then
                Key = Arr(i, 1) & "_" & Arr(i, 2)
                If Not Dic.exists(Key) Then
                    Dic.Add (Key), ""
                End If
            End If
        Next i
        For i = 1 To UBound(Arr1)
            If Arr1(i, 1) <> "" And Arr1(i, 2) <> "" Then
                Key = Arr1(i, 1) & "_" & Arr1(i, 2)
                If Not Dic.exists(Key) Then
                    m = m + 1
                    Res1(m, 1) = Arr1(i, 1)
                    Res1(m, 2) = Arr1(i, 2)
                    Res1(m, 3) = Arr1(i, 3)
                Else
                    k = k + 1
                    Res(k, 1) = Arr1(i, 1)
                    Res(k, 2) = Arr1(i, 2)
                    Res(k, 3) = Arr1(i, 3)
                End If
            End If
        Next i
    End With
    If k Then
        With Sheets("Tontai")
            .Range("E2:G100000").ClearContents
            .Range("E2").Resize(k, 3).Value = Res
        End With
    End If
    If m Then
        With Sheets("Khongtontai")
            .Range("E2:G100000").ClearContents
            .Range("E2").Resize(m, 3).Value = Res1
        End With
    End If
    Set Dic = Nothing
End Sub

 

[Tình nghĩa giang hồ]

Thấy dữ liệu nhiều cái không trùng lắm mà sao trong sheet Khongtontai lại có 2 dòng nhỉ

dữ liệu em cần là cột F, cột G, cột H (do kế toán cung cấp đó anh)
Còn dữ liệu cột A, cột B, mục đích để so sánh thôi. Cột A, B là dữ liệu chuẩn từ phần mềm lấy ra.

Bài đã được tự động gộp: 7/11/22

Không biết phải thế này không?
Kiểm tra lại thử xem nhé!

Option Explicit
Sub GPE()
    Dim Dic As Object, Key$, Arr(), Res(1 To 1000000, 1 To 3)
    Dim Lr1&, Arr1(), i&, k&, m&, Lr&, Res1(1 To 1000000, 1 To 3)
    Set Dic = CreateObject("Scripting.Dictionary")
    With Sheets("Sheet1")
        Lr = .Range("A" & Rows.Count).End(xlUp).Row
        Lr1 = .Range("F" & Rows.Count).End(xlUp).Row
        Arr = .Range("A2:B" & Lr).Value
        Arr1 = .Range("F2:H" & Lr).Value
        For i = 1 To UBound(Arr)
            If Arr(i, 1) <> "" And Arr(i, 2) <> "" Then
                Key = Arr(i, 1) & "_" & Arr(i, 2)
                If Not Dic.exists(Key) Then
                    Dic.Add (Key), ""
                End If
            End If
        Next i
        For i = 1 To UBound(Arr1)
            If Arr1(i, 1) <> "" And Arr1(i, 2) <> "" Then
                Key = Arr1(i, 1) & "_" & Arr1(i, 2)
                If Not Dic.exists(Key) Then
                    m = m + 1
                    Res1(m, 1) = Arr1(i, 1)
                    Res1(m, 2) = Arr1(i, 2)
                    Res1(m, 3) = Arr1(i, 3)
                Else
                    k = k + 1
                    Res(k, 1) = Arr1(i, 1)
                    Res(k, 2) = Arr1(i, 2)
                    Res(k, 3) = Arr1(i, 3)
                End If
            End If
        Next i
    End With
    If k Then
        With Sheets("Tontai")
            .Range("E2:G100000").ClearContents
            .Range("E2").Resize(k, 3).Value = Res
        End With
    End If
    If m Then
        With Sheets("Khongtontai")
            .Range("E2:G100000").ClearContents
            .Range("E2").Resize(m, 3).Value = Res1
        End With
    End If
    Set Dic = Nothing
End Sub

Dạ, em cảm ơn anh nhiều.
Code hoạt động rất tốt.

 

[VetMini]

...
Dạ, em cảm ơn anh nhiều.
Code hoạt động rất tốt.

100 ngàn mã không thể quản lý bằng Excel. Cố gắng VBA chỉ là tình trạng dán băng keo.
Ngay cả phần mêm CSDL chùa (miễn phí) mà gặp cỡ này cho Master file cũng té ngữa. 100 ngàn khách hàng là nói chuyện hàng triệu phát sinh (tôi chỉ nói phát sinh đang ở tình trạng hiệu lực, không kể archived). Nửa năm là không thể kiểm soát sai sót. Vài năm là CSDL chết đứng.

Không thể có nghĩa là không thể. Hết

Và có muốn thêm gì thì cũng là câu hỏi: làm ăn với hằng trăm ngàn khác hàng mà quản lý bằng Excel sao? Công ty nào tồi thế?

 

[Tình nghĩa giang hồ]

100 ngàn mã không thể quản lý bằng Excel. Cố gắng VBA chỉ là tình trạng dán băng keo.
Ngay cả phần mêm CSDL chùa (miễn phí) mà gặp cỡ này cho Master file cũng té ngữa. 100 ngàn khách hàng là nói chuyện hàng triệu phát sinh (tôi chỉ nói phát sinh đang ở tình trạng hiệu lực, không kể archived). Nửa năm là không thể kiểm soát sai sót. Vài năm là CSDL chết đứng.

Không thể có nghĩa là không thể. Hết

Và có muốn thêm gì thì cũng là câu hỏi: làm ăn với hằng trăm ngàn khác hàng mà quản lý bằng Excel sao? Công ty nào tồi thế?

Công ty em có phần mềm SAP (ERP) anh, nhưng mà khi làm gì đa số cũng thích kết xuất ra Excel xử lý.
Sếp em cũng không thích nhân viên dùng Excel, có đợt bắt buộc làm mọi thứ bằng phần mềm. Ai còn làm Excel là phạt, kết quả là nhân viên vẫn lén lút, kết xuất ra Excel làm.
Sau một thời gian hết căng thẳng, mọi người vẫn quay về với Excel hơn, đa số vẫn làm Excel.
Nói chung dù có phần mềm kiểu gì, thì dân văn phòng cũng không thể bỏ Excel, mà em cảm giác bị lệ thuộc Excel nhiều. Nhưng thật sự mà nói, chưa có phần mềm nào linh động hơn Excel.............
Nếu có gì đó tốt hơn Excel thì người ta đã chọn lựa nó rùi.

100 ngàn mã không thể quản lý bằng Excel. Cố gắng VBA chỉ là tình trạng dán băng keo.
Mà câu này em chưa hiểu lắm anh, công ty em có mấy tay kế toán quản trị mấy triệu dòng (khoảng 2-3 triệu dòng) vẫn làm Excel đó anh. Nhưng họ gộp 2-3 file thì phải. Mấy lần ăn trưa họ chia sẽ với em thế.

 

[VetMini]

Công ty em có phần mềm SAP (ERP) anh, nhưng mà khi làm gì đa số cũng thích kết xuất ra Excel xử lý.
Sếp em cũng không thích nhân viên dùng Excel, có đợt bắt buộc làm mọi thứ bằng phần mềm. Ai còn làm Excel là phạt, kết quả là nhân viên vẫn lén lút, kết xuất ra Excel làm.
Sau một thời gian hết căng thẳng, mọi người vẫn quay về với Excel hơn, đa số vẫn làm Excel.

Luật công ty của bạn lỏng lẻo.
Bằng chứng là mấy thằng SAP chúng tự coi mình là trời, và không có chút ý định giúp đỡ các cơ quan (phòng) tương tác. (xem toi phê về ERP bên dưới)

Không phải chính tôi mới là người giải thích cách viết dấu trừ sau số âm hay sao? Mấy thằng SAP chỗ bạn thì chúng chỉ xổ toẹt một câu "tại cách viết của Đức nó vậy". 20 năm trước thì có thể chấp nhận, ngày nay không có phần mềm nào không có khả năng xuất dữ liệu theo dạng tiêu chuẩn cả. SAP là phần mềm sử dụng toàn thế giới. Chuyện ra cái giao diện cà chớn thế là do mấy thằng ấy chúng làm cao. Đáng lẽ, ở nơi quản lý đàng hoàng thì người ta phải tự biết mắc cỡ khi không xuất ra nổi dữ liệu dạng chuẩn. Công ty bạn có "nạn kiêu binh". Đám làm việc với phần mềm đắt tiền khi dễ đám làm việc phần mềm căn bản.

ERP áp dụng như hạch. Một trong những nhiệm vụ của ERP là san bằng cách biệt giao diên các chi nhánh, cơ cấu trong doanh nghiệp. Rõ ràng là nó bất lực trước vấn đề này.

20 năm trước việc chuyển đổi dữ liệu giưa các phần mềm hơi khó, và có loại chuyên viên làm việc này. Tôi cũng từng ăn cơm các doanh nghiệp sử dụng ERP làm công việc này một đôi năm. Nhưng tôi khẳng định, ngày nay công việc này dễ như trở bàn tay.

Nói chung dù có phần mềm kiểu gì, thì dân văn phòng cũng không thể bỏ Excel, mà em cảm giác bị lệ thuộc Excel nhiều. Nhưng thật sự mà nói, chưa có phần mềm nào linh động hơn Excel.............
Nếu có gì đó tốt hơn Excel thì người ta đã chọn lựa nó rùi.

Lý luận hấp tấp quá. Xem xét người đối diện trước đã.
Bản thân tôi trình độ CSDL không thể nói là kém hơn bất cứ người nào trên diễn đàn này. Tôi nói công việc của CSDL thì là CSDL. Tôi có chê bai Excel đâu. Ngược lại, tôi còn đủ tự tin để phân biệt tầm vực cùng giới hạn từng phần mềm. Và tôi chỉ nói "xe cá mập Toyota 24 chỗ ngồi, có chỉnh trang lại cách mấy cũng không thể là xe chở vịt diệu quả. Lãnh thầu mối vịt tuyến đường Cà mau-Sài gòn thì bắt buộc phải dùng xe tài chuyên dụng"
Những người biết tôi ở đây đều hiểu rằng tôi thích Excel hơn Google Sheets mờ.

Nhưng đã biết "chưa có phần mềm nào linh động hơn Excel" mà không chịu khó học hỏi thêm về Data Model và Power Query thì tôi khẳng định là "nói cho lấy có" chứ chân tâm thì không tự tin. Sự linh động mà bạn nói là loại mong cụ nmootj chiếc làm nhiều việc. Ở tầm nhìn cao sức mạnh của Excel nằm ở chỗ MS bỏ công sức thêm nhiều chức năng mới của Excel để đáp ứng phong trào dữ liệu khủng, và các đòi hỏi mới về cách dùng dữ liệu (biến dữ liệu từ dạng thô thành tin tức, dạng dễ nhìn dễ hiểu).
Điển hình, nếu bạn tin vào Excel thì đã chịu khó học hỏi các chức năng, công cụ mới. Người biết Power Query thì đã không phải lên đây hỏi bài này.

Chú thích: Data Model dùng thiết kế và công cụ của SQL Server Express cho nên rất hiệu quả với dữ liệu khủng. Và nếu Power Query không thể lọc/tẩy dữ liệu thì đem MS ra bắn bỏ đi.
MS chọn không chạy đua với Goofle Sheets về các hàm mảng, đặc biệt hàm Query, là để lo chạy trước với Power BI. Tôi tin rằng khi Power BI phát triển đến mức thành mối lo cho Google thì MS mới quay về chuyện hàm Query cho bảng tính Excel.

* Cái lão già Xê Tê đâu biến rồi? vào đề mô cho thớt thấy mình đã "thả mồi bắt bóng", quá chú tâm với VBA cho nên lỡ cơ hội học hỏi những cách xử lý mới xem?

100 ngàn mã không thể quản lý bằng Excel. Cố gắng VBA chỉ là tình trạng dán băng keo.
Mà câu này em chưa hiểu lắm anh, công ty em có mấy tay kế toán quản trị mấy triệu dòng (khoảng 2-3 triệu dòng) vẫn làm Excel đó anh. Nhưng họ gộp 2-3 file thì phải. Mấy lần ăn trưa họ chia sẽ với em thế.

Một sheet chỉ chứa trên dưới 1 triệu dòng. Làm trên con số đó phải biết kỹ thuật "partition/phân chia".

Bảo tôi đoán già đoán non thì có thể mấy người ấy giỏi đám Data Model và Power Query mà tôi nói ở trên.
Câu đoán thứ hai là có thể họ cũng biết vài diễn đàn như GPE (kiểu như GPE, nhưng không nhất thiết là GPE), nhờ làm giùm.