So sánh dữ liệu 2 cột (4 người xem)

[dannd]

Chào các bạn, Mình có 2 bảng dữ liệu như Sheet Data1 và Data2. Mình đang làm thủ công là copy dữ liệu sheet data2 ghép vào data1 và chèn thêm dòng để các dữ liệu tương ứng như sheet DataMerge. Làm thủ công nên bảng dữ liệu 2000 - 3000dòng thì khá mỏi tay và mất thời gian.
Nguyên tắc ở đây là:
- Xử lý trên data2
- So sánh cột dữ liệu "AWB_NO" trong data1 với "AwbNumber" trong data2. Lấy "AWB_NO" trong data1 làm chuẩn.
- Do trong dữ liệu "AWB_NO" trong data1 có thể có 2 dòng trùng nhau (cùng AWB_No, nhưng khác Khối lượng VC, giờ VC, ...), trong khi dữ liệu "AwbNumber" trong data2 chỉ có 1 dòng nên buộc phải chèn thêm 1, 2, ... dòng để đảm bảo các dữ liệu phía sau số "AWB_NO" = "AwbNumber"
- Kết quả sau khi xử lý data2 được coppy và dán vào data1 sẽ như sheet DataMerge trong file đính kèm.
- Dòng dữ liệu có trong data2 nhưng không có trong data1 thì sẽ được highlight và không đưa vào xử lý
Các bạn giúp mình hàm hay công thức xử lý công việc trên với.
Ths.

 

[FatBear]

chắc chắn sheet data2 không có 2 AwbNumber trùng nhau nhé

 

[dannd]

Đúng vậy, data1 thì chắc chắn có, data2 thì chăc chắn không có. Nếu có cũng được kiểm tra và delete trước. Cái đó là bắt buộc.

 

[SA_DQ]

. . . trong khi dữ liệu "AwbNumber" trong data2 chỉ có 1 dòng nên buộc phải chèn thêm 1, 2, ... dòng để đảm bảo các dữ liệu phía sau số "AWB_NO" = "AwbNumber"
. . . .

Muốn làm chuyện chèn dòng (nhất thiết) fải xài VBA
Vì sau đó bạn còn Copy & dán đi đâu đó nữa mà!

 

[FatBear]

Đúng vậy, data1 thì chắc chắn có, data2 thì chăc chắn không có. Nếu có cũng được kiểm tra và delete trước. Cái đó là bắt buộc.

chạy code sau nhé bạn. xem xét kĩ thì dữ liệu và định dạng của file mẫu up lên của bạn có vấn đề. thôi thì xài tạm :v

Sub gopdulieu()
Dim i As Long, j As Long, k As Long
Dim er As Long, er2 As Long
Dim Key As Variant
Dim sArr(), sArr2(), kqArr()
With Sheet2
    er2 = .Range("C" & Rows.Count).End(3).Row
    sArr2 = .Range("A2:N" & er2).Value
End With
With Sheet1
    er = .Range("A" & Rows.Count).End(3).Row
    sArr = .Range("A2:O" & er).Value
End With
With Sheet1
ReDim kqArr(1 To UBound(sArr), 1 To UBound(sArr2, 2))
    For i = 1 To UBound(sArr2)
        Key = sArr2(i, 3)
        For k = 1 To UBound(sArr)
            If Key Like "*" & sArr(k, 1) & "*" Then
                For j = 1 To UBound(sArr2, 2)
                    kqArr(k, j) = sArr2(i, j)
                Next j
                Exit For
            End If
        Next k
    Next i
Sheet1.Range("P2:AC100").ClearContents
Sheet1.Range("P2").Resize(UBound(sArr), UBound(sArr2, 2)) = kqArr
End With
End Sub

 

[SA_DQ]

Do trong dữ liệu "AWB_NO" trong data1 có thể có 2 dòng trùng nhau (cùng AWB_No, nhưng khác Khối lượng VC, giờ VC, ...), trong khi dữ liệu "AwbNumber" trong data2 chỉ có 1 dòng nên buộc phải chèn thêm 1, 2, ... dòng để đảm bảo các dữ liệu phía sau số "AWB_NO" = "AwbNumber"

Thứ nhất, trong cả 2 trang 'Data' không có tiêu đề cột nào là 'Khối lượng VC' hay 'Giờ VC'
Chỉ có ở trang 'Data1' các trường 'KLvanchuyen' & 'GioVC' mà thôi. Nếu bạn muốn nói đến 2 tiêu đề cột này thì cần mở ngoăt để giải thích cho rõ; khỏi mất thời gian người khác & cả của bạn nữa.
Ở trang 'Data2' tình hình còn tồi tệ hơn!

Bạn thử chạy macro này:

Sub SoSanh2CotCua2TrangTinh()
 Dim Cls As Range, sRng As Range, Rng As Range
 Dim W As Byte, Rws As Long, MyColor As Byte
 Dim MyAdd As String
 
 With Sheets("Data1")
    Rws = .[A1].CurrentRegion.Rows.Count
    Set Rng = .[A1].Resize(Rws)
 End With
 Sheets("Data2").Select:                        MyColor = 34
 For Each Cls In Range([C2], [C2].End(xlDown))
    Set sRng = Rng.Find(Cls.Value, , xlFormulas, xlWhole)
    If sRng Is Nothing Then
        Cls.Interior.ColorIndex = 38
    Else
        MyAdd = sRng.Address
        W = 0
        Do
            W = W + 1
            If W > 1 Then _
                sRng.Interior.ColorIndex = MyColor
            MyColor = MyColor + 1:                  If MyColor > 44 Then MyColor = 34
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    End If
 Next Cls
End Sub

sau khi đã bỏ dinh dạng CF

 

[dannd]

Cảm ơn các bạn rất nhiều.
Thực sự hôm qua ngồi bấm chèn dòng mỏi tay quá mới nghĩ ra cầu cứu các bạn giúp đỡ vì vậy phần dữ liệu trích xuất và giải thích chưa được chuẩn, làm khó các bạn, mong accs bạn thông cảm.
Ths.

 

[dannd]

Mình đã dùng file VB (#5) của bạn langtusau9x thì thấy rất ổn. Tuy nhiên có một vấn đề chưa xử lý được đó là Dòng dữ liệu có trong data2 nhưng không có trong data1 thì sẽ được highlight ngay trong data 2.
Các bạn giúp hoàn thiện lại hộ mình với.

 

[FatBear]

Mình đã dùng file VB (#5) của bạn langtusau9x thì thấy rất ổn. Tuy nhiên có một vấn đề chưa xử lý được đó là Dòng dữ liệu có trong data2 nhưng không có trong data1 thì sẽ được highlight ngay trong data 2.
Các bạn giúp hoàn thiện lại hộ mình với.

phần nhập liệu cũng như định dạng ở cột A của sheet "Data1" và cột C của sheet "Data2" không thống nhất giống nhau
Nếu bạn chuyển định dạng của 2 cột này về number hoặc Currency sẽ thấy được sự khác nhau rõ rệt giữa hai cột.
Vậy nên bạn hãy kiểm tra tính thống nhất của nó rồi mới xử lý tiếp phần còn lại được.

 

[HieuCD]

phần nhập liệu cũng như định dạng ở cột A của sheet "Data1" và cột C của sheet "Data2" không thống nhất giống nhau
Nếu bạn chuyển định dạng của 2 cột này về number hoặc Currency sẽ thấy được sự khác nhau rõ rệt giữa hai cột.
Vậy nên bạn hãy kiểm tra tính thống nhất của nó rồi mới xử lý tiếp phần còn lại được.

Dùng hàm CStr chuyển về chuổi
Dùng Dic để tăng tốc code

 

[dannd]

OK, mình đã chuyển về format text như file đính kèm, bạn giúp mình tý nhé.
Ths.

 

[FatBear]

Dùng hàm CStr chuyển về chuổi
Dùng Dic để tăng tốc code

aha.sư phụ à ) quả thật dữ liệu của thớt này rất kì quái. bởi vì nó không thống nhất nên đệ tử đành phải xài toán tử "like" thay vì "=" nếu không thì ko ra được kết quả :3
có gợi ý kia rồi thì vấn đề đã được giải quyết cảm ơn sư phụ nhiều

 

[befaint]

Chào các bạn, Mình có 2 bảng dữ liệu như Sheet Data1 và Data2. Mình đang làm thủ công là copy dữ liệu sheet data2 ghép vào data1 và chèn thêm dòng để các dữ liệu tương ứng như sheet DataMerge. Làm thủ công nên bảng dữ liệu 2000 - 3000dòng thì khá mỏi tay và mất thời gian.
Nguyên tắc ở đây là:
- Xử lý trên data2
- So sánh cột dữ liệu "AWB_NO" trong data1 với "AwbNumber" trong data2. Lấy "AWB_NO" trong data1 làm chuẩn.
- Do trong dữ liệu "AWB_NO" trong data1 có thể có 2 dòng trùng nhau (cùng AWB_No, nhưng khác Khối lượng VC, giờ VC, ...), trong khi dữ liệu "AwbNumber" trong data2 chỉ có 1 dòng nên buộc phải chèn thêm 1, 2, ... dòng để đảm bảo các dữ liệu phía sau số "AWB_NO" = "AwbNumber"
- Kết quả sau khi xử lý data2 được coppy và dán vào data1 sẽ như sheet DataMerge trong file đính kèm.
- Dòng dữ liệu có trong data2 nhưng không có trong data1 thì sẽ được highlight và không đưa vào xử lý
Các bạn giúp mình hàm hay công thức xử lý công việc trên với.
Ths.

Hỏi chủ thớt vài chỗ chưa rõ:

1- Flight Number có ý nghĩa gì?
Thấy bảng kết quả so sánh của bạn, có một số trường hợp AwbNumber trùng AWB_NO nhưng khác Flight Number?
Giả sử trùng AwbNumber và Flight Number nhưng khác khối lượng vận chuyển, giờ vận chuyển thì tính sao?

2- Trong Data1 có trùng AWB_NO, khi lấy AwbNumber ở Data2 so sánh với AWB_NO thì chỉ cần so sánh với AWB_NO đầu tiên (trong các dữ liệu trùng đó)?

3- Không hai lai dữ liệu (có trong Data2 mà không có trong Data1) được không? Mà liệt kê thành một bảng với tiêu đề: "Những thứ có trong Data2 mà không có trong Data1".

4- Sau khi lập được bảng so sánh DataMerge như bạn mong muốn, bạn muốn/ cần làm gì tiếp?

 

[FatBear]

OK, mình đã chuyển về format text như file đính kèm, bạn giúp mình tý nhé.
Ths.

dùng code này nhé bạn. chi tiết xem ở file đính kèm

Sub GPE_DtHCD()
Dim i As Long, j As Long, k As Long, m As Long
Dim er As Long, er2 As Long
Dim sArr() As Variant, sArr2() As Variant, kqArr()
With Sheet2
    er2 = .Range("C" & Rows.Count).End(3).Row
    sArr2 = .Range("A2:N" & er2).Value
End With
With Sheet1
    er = .Range("A" & Rows.Count).End(3).Row
    sArr = .Range("A2:O" & er).Value
End With
ReDim kqArr(1 To UBound(sArr), 1 To UBound(sArr2, 2))
Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(sArr)
        Key = CStr(sArr(i, 1))
        If Not dic.exists(Key) Then 'add key vao dic(sheet data1)
            dic.Item(Key) = i
        End If
    Next i

With Sheet2
    .Range("C2:C" & er2).Interior.Color = xlNone
    For i = 1 To UBound(sArr2)
        Key = CStr(sArr2(i, 3))
                If dic.exists(Key) Then
                    For j = 1 To UBound(sArr2, 2)
                        kqArr(dic.Item(Key), j) = sArr2(i, j)
                    Next j
                Else
                    Sheet2.Cells(i + 1, 3).Interior.ColorIndex = 22
                End If
    Next i
End With
Sheet1.Range("P2").Resize(UBound(sArr), UBound(sArr2, 2)) = kqArr
Set dic = Nothing
End Sub

 

[dannd]

Cảm ơn các bạn đã giúp. Mình lảm rõ một số thắc mắc của các bạn.
1/. Dữ liệu ở đây được xuất từ 2 hệ thống của 2 cty khác nhau, họ gửi về bọn mình xử lý vì vậy định dạng khác nhau. Hơi khoai!

2/. Một Cty tính gộp dữ liệu, một Cty tính chi tiết theo thực tế nên mới có chuyện một AwbNumber có nhiều Flight Number (một đơn hàng chia ra vận chuyển nhiều lần).

3/. Trong Data1 có trùng AWB_NO, khi lấy AwbNumber ở Data2 so sánh với AWB_NO thì chỉ cần so sánh với AWB_NO đầu tiên (trong các dữ liệu trùng đó)? YES

4/. Không hai lai dữ liệu (có trong Data2 mà không có trong Data1) được không? Mà liệt kê thành một bảng với tiêu đề: "Những thứ có trong Data2 mà không có trong Data1": Cũng được, miễn là mình biết và yêu cầu kiểm tra lại trên hệ thống.

5/. Sau khi lập được bảng so sánh DataMerge như bạn mong muốn, bạn muốn/ cần làm gì tiếp: Còn khá nhiều việc lắt nhắt với các trường hợp AWB có "Thành tiền" - "Freight Charge" <>0, các công việc này khó chuẩn hóa.

Ths các bạn.

 

[FatBear]

Cảm ơn các bạn đã giúp. Mình lảm rõ một số thắc mắc của các bạn.
1/. Dữ liệu ở đây được xuất từ 2 hệ thống của 2 cty khác nhau, họ gửi về bọn mình xử lý vì vậy định dạng khác nhau. Hơi khoai!

2/. Một Cty tính gộp dữ liệu, một Cty tính chi tiết theo thực tế nên mới có chuyện một AwbNumber có nhiều Flight Number (một đơn hàng chia ra vận chuyển nhiều lần).

3/. Trong Data1 có trùng AWB_NO, khi lấy AwbNumber ở Data2 so sánh với AWB_NO thì chỉ cần so sánh với AWB_NO đầu tiên (trong các dữ liệu trùng đó)? YES

4/. Không hai lai dữ liệu (có trong Data2 mà không có trong Data1) được không? Mà liệt kê thành một bảng với tiêu đề: "Những thứ có trong Data2 mà không có trong Data1": Cũng được, miễn là mình biết và yêu cầu kiểm tra lại trên hệ thống.

5/. Sau khi lập được bảng so sánh DataMerge như bạn mong muốn, bạn muốn/ cần làm gì tiếp: Còn khá nhiều việc lắt nhắt với các trường hợp AWB có "Thành tiền" - "Freight Charge" <>0, các công việc này khó chuẩn hóa.

Ths các bạn.

về định dạng thì đã được xử lý trong code khi chạy rồi nên giờ ko lo định dạng khác nhau nữa nhé
còn nếu hai lai dữ liệu thì code sẽ chậm hơn. nếu liệt kê danh sách có ở data2 nhưng không có ở data1 thì code sẽ chạy nhanh hơn

 

[dannd]

Hi,
Mình test thử lại rồi, phần highlight vẫn có vấn đề.
Cụ thể data2 có, data 1 (trên data1 khi chạy gộp cũng có) cũng có nhưng data2 vẫn highlight.
Ths.

 

[FatBear]

Hi,
Mình test thử lại rồi, phần highlight vẫn có vấn đề.
Cụ thể data2 có, data 1 (trên data1 khi chạy gộp cũng có) cũng có nhưng data2 vẫn highlight.
Ths.

mình xem lại kết quả vẫn đúng
bạn chỉ rõ chỗ sai, mã nào sai để mình kiểm tra lại

 

[dannd]

OK, Mình copy code nên có nhầm lẫn. Test lại OK rùi.
Cảm ơn bạn nhiều.

 

[befaint]

4/. Không hai lai dữ liệu (có trong Data2 mà không có trong Data1) được không? Mà liệt kê thành một bảng với tiêu đề: "Những thứ có trong Data2 mà không có trong Data1": Cũng được, miễn là mình biết và yêu cầu kiểm tra lại trên hệ thống.

Vậy phương án liệt kê ra để kiểm tra là hợp lý hơn cả.
Bởi bạn tô màu để đánh dấu rồi lại lọc để lấy chúng (nếu không cũng mỏi mắt, mỏi tay để tìm và nhặt chúng sau khi đã tô màu).