Tự động dò tìm & gán dữ liệu giữa các sheet theo mã hàng & đánh dấu dữ liệu đã gán (1 người xem)

thanhhoadaica · 24/11/14

Xin chào GPE!!!

Hiện tại mình đang gặp phải một vấn đề rất mong nhận được sự giúp đỡ:

Mình có 2 sheet: sheet 1 và sheet 2 đều có 1 cột là “Mã hàng” mình muốn so sánh 2 cột này với nhau.

Đặc điểm 2 cột “Mã hàng” mình cần so sánh là :

1. Các “Mã hàng” trên 2 sheet nằm ngẫu nhiên (không theo thứ tự)
2. “Mã hàng” này bên sheet 1 có nhưng bên sheet 2 lại không có và ngược lại
3. Trong cùng một sheet có thể có nhiều dòng “Mã hàng” trùng lắp

Làm thế nào khi lập công thức so sánh đạt được những tiêu chí sau:

1. “Mã hàng” nào có ở cả 2 sheet thì kết quả trả về “Có”, “Mã hàng” nào không có kết quả trả về “Không”
2. Nếu trên sheet 1 có 2 “Mã hàng” trùng lắp (2 dòng giống nhau) nhưng trên sheet 2 cũng cùng “Mã hàng” này nhưng chỉ có một dòng thì trả về kết quả “Thừa” hoặc “Thiếu”
Vì đây là một bảng dữ liệu gần đến 6.000 dòng nên mình không biết phải dò bằng cách nào, các bạn giúp mình nhé, nếu được cho mình hướng dẫn với, mình xin cảm ơn chân thành!!!

vu_tuan_manh_linh · 24/11/14

thanhhoadaica đã viết:
Xin chào GPE!!!

Hiện tại mình đang gặp phải một vấn đề rất mong nhận được sự giúp đỡ:

Mình có 2 sheet: sheet 1 và sheet 2 đều có 1 cột là “Mã hàng” mình muốn so sánh 2 cột này với nhau.

Đặc điểm 2 cột “Mã hàng” mình cần so sánh là :

1. Các “Mã hàng” trên 2 sheet nằm ngẫu nhiên (không theo thứ tự)
2. “Mã hàng” này bên sheet 1 có nhưng bên sheet 2 lại không có và ngược lại
3. Trong cùng một sheet có thể có nhiều dòng “Mã hàng” trùng lắp

Làm thế nào khi lập công thức so sánh đạt được những tiêu chí sau:

1. “Mã hàng” nào có ở cả 2 sheet thì kết quả trả về “Có”, “Mã hàng” nào không có kết quả trả về “Không”
2. Nếu trên sheet 1 có 2 “Mã hàng” trùng lắp (2 dòng giống nhau) nhưng trên sheet 2 cũng cùng “Mã hàng” này nhưng chỉ có một dòng thì trả về kết quả “Thừa” hoặc “Thiếu”
Vì đây là một bảng dữ liệu gần đến 6.000 dòng nên mình không biết phải dò bằng cách nào, các bạn giúp mình nhé, nếu được cho mình hướng dẫn với, mình xin cảm ơn chân thành!!!

Xem file nhé bạn!!!! Chỉ đơn giản là cách lồng các điều kiện và hàm if() kết hợp countif().

thanhhoadaica · 24/11/14

Cảm ơn bạn đã trả lời! mình đã download file kết quả của bạn về nhưng không chính xác bạn ơi.

Let'GâuGâu · 24/11/14

thanhhoadaica đã viết:
Cảm ơn bạn đã trả lời! mình đã download file kết quả của bạn về nhưng không chính xác bạn ơi.

bạn cho kết quả bạn muốn vào file đi. nói là ko chính xác thì ai mà biết được.
câu thứ 2 ở bài 1. khi nào thì xảy ra trường hợp thiếu?

mhung12005 · 25/11/14

thanhhoadaica đã viết:
Cảm ơn bạn đã trả lời! mình đã download file kết quả của bạn về nhưng không chính xác bạn ơi.

Bạn giải thích chưa rõ ràng lắm nên mọi người không biết giúp bạn sao cho đúng ý.
Mình đoán bạn muốn so sánh 2 danh sách để tìm ra hiện trạng của từng mã hàng nên làm thế này. Bạn xem file xem có đúng ý không nhé.

thanhhoadaica · 25/11/14

Mình xin mô tả:
Cty mình làm việc trong lĩnh vực bán hàng điện thoại di động (DTDĐ) có sử dụng phần mềm bán hàng (PMBH), vì xài lâu rồi nên sếp y/c giải phóng dữ liệu để hệ thống nhẹ nhàng hơn (nghĩa là xóa bỏ dữ liệu cũ và chỉ nhập lại hàng tồn kho hiện tại có kèm theo imei của từng mặt hàng đó) sẽ là đơn giản nếu tổng số lượng hàng tồn của từng mặt hàng = tổng số IMEI của mặt hàng đó, nhưng vì PMBH chưa ràng buộc tốt về vấn đề IMEI nên xảy ra tình trạng sau:
1. Tổng số lượng > tổng số IMEI
2. Tổng SL < tổng số IMEI (do lúc xuất bán NV chỉ xuất số lượng mà k xuất IMEI)
3. PMBH không có công cụ nào để biết được mặt hàng nào thì có tổng SL = tổng Số IMEI, k kiểm tra được mặt hàng nào có thừa IMEI cũng như thiếu IMEI
Từ khó khăn này mình mới nghĩ ra thêm một khó khăn khác vậy mình sẽ đi tìm lời giải cho vđ nêu trên gồm ba bước:
B1: Xuất từ hệ thống để lấy tồn kho ví dụ mã hàng A có số lượng 2 dùng ct exel tách ra 2 dòng A A
B2: Xuất từ hệ thống tồn kho IMEI: nếu có 1 số IMEI sẽ được 1 dòng mặt hàng A,…..
B3: Dùng ct excel check 2 file excel trên xem:
TH1: Nếu 2 file excel đều có chứa cùng mặt hàng và có số dòng bằng nhau trả về kết quả khớp
TH2: Nếu file excel 1 có chứa nhưng file excel 2 không có chứa trả về: chỉ có ở 1 hoặc chỉ có ở 2
TH3: Nếu số dòng lệch nhau lấy 1 sheet làm chuần báo sheet kia thừa hay thiếu bao nhiêu.
Xin cảm ơn mọi người đã quan tâm!!!!

mhung12005 · 25/11/14

thanhhoadaica đã viết:
Mình xin mô tả:
Cty mình làm việc trong lĩnh vực bán hàng điện thoại di động (DTDĐ) có sử dụng phần mềm bán hàng (PMBH), vì xài lâu rồi nên sếp y/c giải phóng dữ liệu để hệ thống nhẹ nhàng hơn (nghĩa là xóa bỏ dữ liệu cũ và chỉ nhập lại hàng tồn kho hiện tại có kèm theo imei của từng mặt hàng đó) sẽ là đơn giản nếu tổng số lượng hàng tồn của từng mặt hàng = tổng số IMEI của mặt hàng đó, nhưng vì PMBH chưa ràng buộc tốt về vấn đề IMEI nên xảy ra tình trạng sau:
1. Tổng số lượng > tổng số IMEI
2. Tổng SL < tổng số IMEI (do lúc xuất bán NV chỉ xuất số lượng mà k xuất IMEI)
3. PMBH không có công cụ nào để biết được mặt hàng nào thì có tổng SL = tổng Số IMEI, k kiểm tra được mặt hàng nào có thừa IMEI cũng như thiếu IMEI
Từ khó khăn này mình mới nghĩ ra thêm một khó khăn khác vậy mình sẽ đi tìm lời giải cho vđ nêu trên gồm ba bước:
B1: Xuất từ hệ thống để lấy tồn kho ví dụ mã hàng A có số lượng 2 dùng ct exel tách ra 2 dòng A A
B2: Xuất từ hệ thống tồn kho IMEI: nếu có 1 số IMEI sẽ được 1 dòng mặt hàng A,…..
B3: Dùng ct excel check 2 file excel trên xem:
TH1: Nếu 2 file excel đều có chứa cùng mặt hàng và có số dòng bằng nhau trả về kết quả khớp
TH2: Nếu file excel 1 có chứa nhưng file excel 2 không có chứa trả về: chỉ có ở 1 hoặc chỉ có ở 2
TH3: Nếu số dòng lệch nhau lấy 1 sheet làm chuần báo sheet kia thừa hay thiếu bao nhiêu.
Xin cảm ơn mọi người đã quan tâm!!!!

Thế thì bạn test thủ tục trong #5 xem đã đúng ý bạn chưa, nếu chưa thì tính tiếp.

Note: Mình thấy có IMEI tồn tại ở cả 2 sheet mỗi sheet 2 lần thì bạn tính sao ?

thanhhoadaica · 26/11/14

Cảm ơn bạn mhung12005 đã giúp đỡ mình

Trong hệ thống mình xuất ra có hai file, file thứ nhất chỉ có mã hàng và sl như file mình upload bên dưới, file thứ hai thì có mã hàng và số IMEI giá nhập kèm theo mã hàng đó, sau khi dùng công thức check, nếu mã hàng cùng tồn tại và có số lần xuất hiện bằng nhau ở cả hai file mình sẽ import lại vào phần mềm.

mhung12005 · 26/11/14

thanhhoadaica đã viết:
Cảm ơn bạn mhung12005 đã giúp đỡ mình

Trong hệ thống mình xuất ra có hai file, file thứ nhất chỉ có mã hàng và sl như file mình upload bên dưới, file thứ hai thì có mã hàng và số IMEI giá nhập kèm theo mã hàng đó, sau khi dùng công thức check, nếu mã hàng cùng tồn tại và có số lần xuất hiện bằng nhau ở cả hai file mình sẽ import lại vào phần mềm.

Không phải chỉ chạy 68 dòng đâu bạn, mà trong 2 danh sách chỉ có 68 mã hàng thôi. Phải liệt kê danh sách mã hàng đó trước sau đó mới xét đến hiện trạng của nó chứ.

Mình làm thêm phần số lượng từng mã hàng trong từng danh sách để bạn dễ hiểu thông tin hơn. Bạn xem file đính kèm nhé.

VD:
Mã hàng: 301Black --> Có trong cả 2 danh sách: Danh sách 1 là 10 lần; Danh sách 2 là 10 lần.

thanhhoadaica · 5/12/14

Mình rất cảm ơn sự giúp đỡ nhiệt tình từ bạn mhung12005 từ giúp đỡ của bạn mhung12005 mình đã có thể so sánh mã hàng từ hai sheet cho trước hai sheet có tên lần lượt là 1,2 file excel “kequa.xls” có kết quả trả về như bên dưới:

Mã hàng đã so sánh	Tình trạng
MHA	- Có trong 1 không có trong 2
MHB	- Có trong 2 không có trong 1
MHC	- Thiếu (nếu mã hàng xuất hiện bên 2 nhiều lần hơn bên 1)
MHD	- Thừa (nếu mã hàng xuất hiện bên 1 nhiều lần hơn bên 2)
MHE	- Có trong cả 2 danh sách

Mình gặp khó khăn như sau: sau khi so sánh chúng ta có được một file excel với kết quả như trên, mình cần trích lọc kết quả trên với hai file excel khác nữa hai file excel này chứa dữ liệu như mô tả bên dưới:

1. File thứ nhất “tonkho-soluong.xls” chứa danh sách các mặt hàng bao gồm các cột: Mã hàng,tên hàng, số lượng, đơn giá

2. File thứ hai “tonkho-imei.xls” chứa danh sách IMEI bao gồm các cột: Mã hàng, IMEI, bảo hành, giá nhập

Ý mình là làm thế nào để có thể kết nối 3 file excel này lại với nhau thông qua cột “Mã hàng” nghĩa là:

- Nếu mình chọn kết nối giữa file excel “ketqua.xls” với file “tonkho-soluong.xls” thì trên file “kết quả” nếu mình chọn điều kiện lọc là “Có trong 1 không có trong 2” thì ở file “tonkho-soluong.xls” sẽ chỉ view lên những dòng mã hàng đã được lọc ở file “ketqua.xls”
- Nếu mình chọn kết nối giữa file excel “ketqua.xls” với file “tonkho-imei.xls” thì trên file excel “ketqua.xls” nếu mình chọn điều kiện lọc “Có trong cả 2 danh sách” thì trên file excel “tonkho- imei.xls” sẽ chỉ view lên những dòng mã hàng đã được lọc ở file excel “ketqua.xls”
(ví dụ: ở file excel “ketqua.xls” khi chọn điều kiện lọc là “có trong cả 2 danh sách” trả về 2 mã hàng MHA, MHB, thì trên file “tonkho-imei.xls” sẽ chỉ hiển thị những dòng hàng có mã hàng là MHA, MHB)
- Tương tự cho các điều kiện lọc “Có trong 2 không có trong 1”, “Thiếu”

Vì số lượng mã hàng có thể lên tới hơn một nghìn dòng nên việc làm bằng tay chắc là không thể.
Rất mong sự hồi âm giúp đỡ từ mọi người, chân thành cảm ơn!!!

thanhhoadaica · 9/12/14

Cho mình xin ý kiến với các bạn.

thanhhoadaica · 10/12/14

Các bạn ui, nếu mình không liên kết giữa các file, mà mình sẽ copy các file này vào mỗi sheet khác nhau thì có cách nào k?

thanhhoadaica · 10/12/14

Bạn xem giúp mình bài viết ở #10 nhé! mình đang gặp rắc rối ở bước tiếp theo!!!

thanhhoadaica · 11/12/14

Cảm ơn bạn đã cho mình hướng giải quyết, nhưng số liệu của mình rất nhiều bạn ơi, mình ước tính có thể lên tới gần 100 file như vậy (cứ một file lại đem đi so sánh sẽ mất nhiều thời gian, trong khi việc này lúc làm thực tế sẽ k có nhiều thời gian để mình làm, đồng thời nhiều thao tác quá sẽ k chính xác nữa), theo mình thì làm thế nào với 3 file excel trên, hoặc có thể copy cho vào 1 file có 3 sheet rồi chỉ cần lọc ở sheet ket qua các sheet còn lại sẽ lọc theo như vậy sẽ hiệu quả hơn lúc mình làm thực tế, trong một đêm mình phải giải quyết hết gần 100 file như vậy là điều k thế?

thanhhoadaica · 11/12/14

Bạn mhung12005 ơi trong file mẫu nhập imei can.xls nếu mình chỉ có một dòng A2 thôi thì không so sánh được? phải có ít nhất 2 dòng thì mới so sánh được, bạn vui lòng chỉ giúp mình cần chỉnh gì trong code vba nhé!!! Thanks bạn rất nhiều!!!!

thanhhoadaica · 12/12/14

bạn mhung12005 ới ời

thanhhoadaica · 12/12/14

có ai chưa ngủ k? giúp mình với

thanhhoadaica · 13/12/14

Nếu IMEI có xuất hiện 2 lần mình sẽ làm thêm một bước kiểm tra trước khi dùng đến bước này, rất cảm ơn bạn, bạn xem giúp mình với nếu ở sheet 2 ô A2 mình chỉ có một mã hàng thì công thức không chạy được bạn giúp mình nhé!

PHP:

Option Explicit

Sub test()
    
    Dim Dic1 As Object, Dic2 As Object
    Dim SrcArr1, SrcArr2, TotalArr, Item, ItemArr, ResArr()
    Dim sTmp As String
    Dim lR As Long, i As Long, k As Long
    
    Set Dic1 = CreateObject("Scripting.Dictionary")
    Set Dic2 = CreateObject("Scripting.Dictionary")
    SrcArr1 = Sheet1.Range(Sheet1.Range("A2"), Sheet1.Range("A65000").End(xlUp)).Value2
    SrcArr2 = Sheet2.Range(Sheet2.Range("A2"), Sheet2.Range("A65000").End(xlUp)).Value2
    If IsArray(SrcArr1) And IsArray(SrcArr2) Then
        TotalArr = Array(SrcArr1, SrcArr2)
    Else
        Exit Sub
    End If
    
    For i = 0 To 1
        For Each Item In TotalArr(i)
            If Len(Item) Then
                sTmp = CStr(Item)
                If Not Dic1.exists(sTmp) Then Dic1.Add sTmp, ""
            End If
        Next Item
    Next i
    If Dic1.Count Then ItemArr = Dic1.keys
    Dic1.RemoveAll
    
    For lR = 1 To UBound(SrcArr1, 1)
        If Len(SrcArr1(lR, 1)) Then
            sTmp = CStr(SrcArr1(lR, 1))
            If Not Dic1.exists(sTmp) Then
                Dic1.Add sTmp, 1
            Else
                Dic1.Item(sTmp) = Dic1.Item(sTmp) + 1
            End If
        End If
    Next lR
    
    For lR = 1 To UBound(SrcArr2, 1)
        If Len(SrcArr2(lR, 1)) Then
            sTmp = CStr(SrcArr2(lR, 1))
            If Not Dic2.exists(sTmp) Then
                Dic2.Add sTmp, 1
            Else
                Dic2.Item(sTmp) = Dic2.Item(sTmp) + 1
            End If
        End If
    Next lR
    
    ReDim ResArr(1 To UBound(ItemArr) + 1, 1 To 2)
    For Each Item In ItemArr
        k = k + 1
        sTmp = CStr(Item)
        ResArr(k, 1) = sTmp
        If Dic1.exists(sTmp) And Not Dic2.exists(sTmp) Then
            ResArr(k, 2) = "Co trong 1, ko co trong 2"
        ElseIf Dic2.exists(sTmp) And Not Dic1.exists(sTmp) Then
            ResArr(k, 2) = "Co trong 2, ko co trong 1"
        ElseIf CLng(Dic1.Item(sTmp)) > 1 And CLng(Dic2.Item(sTmp)) = 1 Then
            ResArr(k, 2) = "Thua"
        ElseIf CLng(Dic2.Item(sTmp)) > 1 And CLng(Dic1.Item(sTmp)) = 1 Then
            ResArr(k, 2) = "Thieu"
        Else
            ResArr(k, 2) = "Co trong ca 2 danh sach"
        End If
    Next Item
    Sheet2.Range("D2").Resize(k, 2).Value = ResArr
    
End Sub

Tự động dò tìm & gán dữ liệu giữa các sheet theo mã hàng & đánh dấu dữ liệu đã gán (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

linhvtm84@gmail.com

File đính kèm

Thành viên mới

Thành viên mới

Thành viên chậm chạm

File đính kèm

Thành viên mới

Thành viên chậm chạm

Thành viên mới

File đính kèm

Thành viên chậm chạm

File đính kèm

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

Thành viên mới

File đính kèm

Thời gian đếm ngược.