Cần giúp đỡ về lọc dữ liệu trùng (5 người xem)

  • Thread starter Thread starter DarKLov3
  • Ngày gửi Ngày gửi
Liên hệ QC

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

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
D

DarKLov3

Thành viên chính thức
Tham gia
20/12/10
Bài viết
68
Được thích
22
Chả là em đang phải rà soát thẻ bảo hiểm y tế bị cấp trùng. Trong dữ liệu hơn 50.000 dòng e đã lọc được các dữ liệu trùng nhau về tên họ và ngày tháng năm sinh ra 1 file, nhưng để biết chắc là có bị cấp trùng thẻ hay không thì với mỗi cặp họ tên giống nhau em phải so sánh dữ liệu trên cột J và K:
Nếu khoảng thời gian từ ngày- đến ngày của 2 dòng (hoặc 3, 4..) mà bị trùng lên nhau toàn bộ hoặc 1 phần thì bôi màu đỏ.
Ví dụ khoảng thời gian trùng nhau:
dòng1: 01/01/2018 - 01/01/2019
Dòng 2: 01/01/2018-29/11/2018
hoặc:
Dòng 1: 01/01/2018-01/01/2019
Dòng 2: 01/01/2017 - 01/6/2018
Dữ liệu không bị trùng là thời gian không có ngày nào bị trùng nhau.
E tham khảo trên mạng thì có cách dùng condition formating nhưng e chưa rành lắm. Mong mọi người có cách nào thì hướng dẫn giúp e với ạ. Làm thủ công với file hơn 6.000 dòng thế này chắc e chết.
Không biết trường hợp này có ai dùng được code VBA không ạ. hic
 

File đính kèm

DarKLov3 đã viết:
Chả là em đang phải rà soát thẻ bảo hiểm y tế bị cấp trùng. Trong dữ liệu hơn 50.000 dòng e đã lọc được các dữ liệu trùng nhau về tên họ và ngày tháng năm sinh ra 1 file, nhưng để biết chắc là có bị cấp trùng thẻ hay không thì với mỗi cặp họ tên giống nhau em phải so sánh dữ liệu trên cột J và K:
Nếu khoảng thời gian từ ngày- đến ngày của 2 dòng (hoặc 3, 4..) mà bị trùng lên nhau toàn bộ hoặc 1 phần thì bôi màu đỏ.
Ví dụ khoảng thời gian trùng nhau:
dòng1: 01/01/2018 - 01/01/2019
Dòng 2: 01/01/2018-29/11/2018
hoặc:
Dòng 1: 01/01/2018-01/01/2019
Dòng 2: 01/01/2017 - 01/6/2018
Dữ liệu không bị trùng là thời gian không có ngày nào bị trùng nhau.
E tham khảo trên mạng thì có cách dùng condition formating nhưng e chưa rành lắm. Mong mọi người có cách nào thì hướng dẫn giúp e với ạ. Làm thủ công với file hơn 6.000 dòng thế này chắc e chết.
Không biết trường hợp này có ai dùng được code VBA không ạ. hic
Nhấp chuột vào đây để mở rộng...
Bạn chạy thử code này xem sao. Cái nào có trùng thì ghi tên, ngược lại thì không. Kết quả điền vào cột U
Mã: 
Option Explicit

Sub DanhDau()
Dim Nguon
Dim Dong
Dim Ten, Mang
Dim Dau, Cuoi
Dim Kq
Dim i, j, k, x
Nguon = Sheet1.Range("a1").CurrentRegion
Dong = UBound(Nguon)
ReDim Kq(1 To Dong, 1 To 1)
i = 2
Do While i < Dong
    Ten = Nguon(i, 2)
    Mang = Split(Ten)
    k = UBound(Mang)
    j = i
    Do While Nguon(j, 2) = Ten
        Dau = Nguon(j, 10)
        Cuoi = Nguon(j, 11)
        x = i
        Do While Nguon(x, 2) = Ten
            If x <> j Then
                If Nguon(x, 10) >= Dau And Nguon(x, 10) <= Cuoi Then
                    Kq(j, 1) = Mang(k)
                    Kq(x, 1) = Mang(k)
                End If
                If Nguon(x, 11) >= Dau And Nguon(x, 11) <= Cuoi Then
                    Kq(j, 1) = Mang(k)
                    Kq(x, 1) = Mang(k)
                End If
            End If
            x = x + 1
            If x > Dong Then Exit Do
        Loop
        j = j + 1
        If j > Dong Then Exit Do
    Loop
    i = j
Loop
Sheet1.Range("u1").Resize(UBound(Kq), UBound(Kq, 2)).ClearContents
Sheet1.Range("u1").Resize(UBound(Kq), UBound(Kq, 2)) = Kq
End Sub
 
DarKLov3 đã viết:
E tham khảo trên mạng thì có cách dùng condition formating nhưng e chưa rành lắm. Mong mọi người có cách nào thì hướng dẫn giúp e với ạ. Làm thủ công với file hơn 6.000 dòng thế này chắc e chết.
Nhấp chuột vào đây để mở rộng...
Tham khảo cách dùng cột phụ và Conditional Formatting như file kèm.
Công thức cột phụ:
Mã: 
T2=IF(COUNTIF($B$2:B2,B2)=1,OR(IFERROR(MATCH(--J2:K2,J3:K3+{0,1}),)=1)+OR(IFERROR(MATCH(--J3:K3,J2:K2+{0,1}),)=1),T1)
Kết thúc bằng Ctrl+Shift+Enter.

Thân
 

File đính kèm

Em tham khảo trên mạng thì cũng dùng cột phụ và có viết đc công thức thế này. tuy nhiên số dữ liệu trùng ra kết quả khác với bác @Phan Thế Hiệp. Số kết quả của e tương ứng với False là 443 dòng.
Các bác kiểm tra giúp e công thức nào chuẩn hơn với ạ.
E gửi kèm file ạ

OR(MMULT(MATCH(N(OFFSET($T$1;AGGREGATE(15;6;ROW($1:$6800)/($B2=$B$2:$B$6800)/(ROW($B$2:$B$6800)<>ROW());ROW(INDIRECT("1:"&COUNTIF($B$2:$B$6800;$B2)-1)));{0\1}));CHOOSE({1\2\3};0;$T2;$U2+1));{1;1})={2\6})
 

File đính kèm

DarKLov3 đã viết:
Em tham khảo trên mạng thì cũng dùng cột phụ và có viết đc công thức thế này. tuy nhiên số dữ liệu trùng ra kết quả khác với bác @Phan Thế Hiệp. Số kết quả của e tương ứng với False là 443 dòng.
Các bác kiểm tra giúp e công thức nào chuẩn hơn với ạ.
E gửi kèm file ạ

OR(MMULT(MATCH(N(OFFSET($T$1;AGGREGATE(15;6;ROW($1:$6800)/($B2=$B$2:$B$6800)/(ROW($B$2:$B$6800)<>ROW());ROW(INDIRECT("1:"&COUNTIF($B$2:$B$6800;$B2)-1)));{0\1}));CHOOSE({1\2\3};0;$T2;$U2+1));{1;1})={2\6})
Nhấp chuột vào đây để mở rộng...
Bài của bạn mở lên toàn thấy #Value! Nên không test được. Còn của bác Hiệp thấy dòng 28, 29 là đúng nhưng của bác Hiệp báo sai. Các dòng khác chưa test
 
DarKLov3 đã viết:
Chả là em đang phải rà soát thẻ bảo hiểm y tế bị cấp trùng. Trong dữ liệu hơn 50.000 dòng e đã lọc được các dữ liệu trùng nhau về tên họ và ngày tháng năm sinh ra 1 file, nhưng để biết chắc là có bị cấp trùng thẻ hay không thì với mỗi cặp họ tên giống nhau em phải so sánh dữ liệu trên cột J và K:
Nếu khoảng thời gian từ ngày- đến ngày của 2 dòng (hoặc 3, 4..) mà bị trùng lên nhau toàn bộ hoặc 1 phần thì bôi màu đỏ.
Ví dụ khoảng thời gian trùng nhau:
dòng1: 01/01/2018 - 01/01/2019
Dòng 2: 01/01/2018-29/11/2018
hoặc:
Dòng 1: 01/01/2018-01/01/2019
Dòng 2: 01/01/2017 - 01/6/2018
Dữ liệu không bị trùng là thời gian không có ngày nào bị trùng nhau.
E tham khảo trên mạng thì có cách dùng condition formating nhưng e chưa rành lắm. Mong mọi người có cách nào thì hướng dẫn giúp e với ạ. Làm thủ công với file hơn 6.000 dòng thế này chắc e chết.
Không biết trường hợp này có ai dùng được code VBA không ạ. hic
Nhấp chuột vào đây để mở rộng...
1/ Dữ liệu trong 2 cột J, K của bạn là Text không phải Date, phải chuyển thành đúng kiểu Date mới xét được.
2/ Bạn có chắc là mỗi người đều có 2 dòng cùng tên, ngày sinh?
Trong file này, tôi viết code theo 2 điều kiện trên, 2 cột J, K dòng nào trùng khoảng thời gian với dòng bên trên thì tô màu.
 

File đính kèm

CHAOQUAY đã viết:
Còn của bác Hiệp thấy dòng 28, 29 là đúng nhưng của bác Hiệp báo sai. Các dòng khác chưa test
Nhấp chuột vào đây để mở rộng...
Sao tôi thấy dòng 28 29 file tôi gửi đúng rồi mà! :)
DoTrung.png

DarKLov3 đã viết:
Các bác kiểm tra giúp e công thức nào chuẩn hơn với ạ.
Nhấp chuột vào đây để mở rộng...
Chuẩn hay không! bạn xem lại file tôi đính kèm bài #3, các dòng tô vàng là bị trùng thời gian, bạn kiểm tra bằng mắt đi.

Công thức kia bạn đem lên, tôi thấy quen quen, (hổng biết phải "con" tôi không!? :) ), nếu đúng là "quen" chắc là tạo trong 1 ngữ cảnh khác, bạn nên xem kỹ lại.


Thân
 
Phan Thế Hiệp đã viết:
Sao tôi thấy dòng 28 29 file tôi gửi đúng rồi mà! :)
View attachment 214919


Chuẩn hay không! bạn xem lại file tôi đính kèm bài #3, các dòng tô vàng là bị trùng thời gian, bạn kiểm tra bằng mắt đi.

Công thức kia bạn đem lên, tôi thấy quen quen, (hổng biết phải "con" tôi không!? :) ), nếu đúng là "quen" chắc là tạo trong 1 ngữ cảnh khác, bạn nên xem kỹ lại.


Thân
Nhấp chuột vào đây để mở rộng...
Bác xem thử clip ở dưới. Cũng không biết là lỗi ở đâu nữa.
Bó tay.
 

File đính kèm

Phan Thế Hiệp đã viết:
Chuẩn hay không! bạn xem lại file tôi đính kèm bài #3, các dòng tô vàng là bị trùng thời gian, bạn kiểm tra bằng mắt đi.
Nhấp chuột vào đây để mở rộng...
Vâng em nhìn file bác giúp thì cũng biết đối với giá trị cột phụ là 1, 2 là có khả năng trùng, + với bác bôi vàng cho rồi.
Tuy nhiên, kiểm tra sơ qua thì vẫn có những chỗ chưa sát.
214920

Ví dụ tại dòng 135 với 137: khoảng thời gian thẻ thứ nhất: 23/12/2017-05/3/2018
Khoảng thời gian thẻ thứ hai: 01/12/2018 - 05/3/2019
=>>>Thì 2 khoảng thời gian này là không phải trùng nhau ạ

Phan Thế Hiệp đã viết:
Công thức kia bạn đem lên, tôi thấy quen quen, (hổng biết phải "con" tôi không!? :) ), nếu đúng là "quen" chắc là tạo trong 1 ngữ cảnh khác, bạn nên xem kỹ lại.
Thân
Nhấp chuột vào đây để mở rộng...
Công thức này theo bài https://www.giaiphapexcel.com/diend...ến-tháng-không-trùng-nhau.139306/#post-894014 đúng là sản phẩm của bác rồi.
e vận dụng tính toán những dữ liệu không trùng. (true) thì những cái còn lại là trùng (False), nhưng ko biết có chắc chắn áp dụng cho trường hợp file này không. hi
Bác kiểm tra lại công thức giùm em với ạ.
 
CHAOQUAY đã viết:
Bác xem thử clip ở dưới. Cũng không biết là lỗi ở đâu nữa.
Bó tay.
Nhấp chuột vào đây để mở rộng...
Chắc do định dạng mặc định của tôi và bạn khác nhau. Tôi đang dùng định dạng hệ thống như mặc định ban đầu (hệ US).

DarKLov3 đã viết:
Ví dụ tại dòng 135 với 137: khoảng thời gian thẻ thứ nhất: 23/12/2017-05/3/2018
Khoảng thời gian thẻ thứ hai: 01/12/2018 - 05/3/2019
Nhấp chuột vào đây để mở rộng...
À! do trùng tên, khác ngày sinh, nên dòng không phải là trên dưới liền nhau 1 cặp.
Bạn nên Sort theo tên và ngày sinh trước khi thực hiện công thức.

Thân
 
@DarKLov3
Dữ liệu tại cột J, K có lẽ không phải dạng Date như bài 6 có nói nên kết quả code bài 2 không đúng.
Sửa lại dữ liệu về date, thêm bôi màu vào sheet, gửi thớt tham khảo.
Kết quả điền vào cột U, trống là đúng, ngược lại là trùng
Mã: 
Option Explicit

Sub DanhDau()
Dim Nguon
Dim Dong
Dim Ten, Mang
Dim Dau, Cuoi
Dim Kq
Dim i, j, k, x
Nguon = Sheet1.Range("a1").CurrentRegion
Sheet1.Range("a1").CurrentRegion.Interior.ColorIndex = 0
Dong = UBound(Nguon)
'Chuyen ve Date
For i = 2 To Dong
    Mang = Split(Nguon(i, 10), "/")
    j = Mang(0): k = Mang(1): x = CLng(Right(Mang(2), 2))
    Nguon(i, 10) = DateSerial(x, k, j)
    
    Mang = Split(Nguon(i, 11), "/")
    j = Mang(0): k = Mang(1): x = CLng(Right(Mang(2), 2))
    Nguon(i, 11) = DateSerial(x, k, j)
Next i
'Xong Chuyen ve Date

ReDim Kq(1 To Dong, 1 To 1)
i = 2
Do While i < Dong
    Ten = Nguon(i, 2)
    Mang = Split(Ten)
    k = UBound(Mang)
    j = i
    Do While Nguon(j, 2) = Ten
        Dau = Nguon(j, 10)
        Cuoi = Nguon(j, 11)
        x = i
        Do While Nguon(x, 2) = Ten
            If x <> j Then
                If Nguon(x, 10) >= Dau And Nguon(x, 10) <= Cuoi Then
                    Kq(j, 1) = Mang(k)
                    Kq(x, 1) = Mang(k)
                    Sheet1.Range("j" & x).Interior.ColorIndex = 6
                End If
                If Nguon(x, 11) >= Dau And Nguon(x, 11) <= Cuoi Then
                    Kq(j, 1) = Mang(k)
                    Kq(x, 1) = Mang(k)
                    Sheet1.Range("k" & x).Interior.ColorIndex = 6
                End If
            End If
            x = x + 1
            If x > Dong Then Exit Do
        Loop
        j = j + 1
        If j > Dong Then Exit Do
    Loop
    i = j
Loop
Sheet1.Range("u1").Resize(UBound(Kq), UBound(Kq, 2)).ClearContents
Sheet1.Range("u1").Resize(UBound(Kq), UBound(Kq, 2)) = Kq
End Sub
 
Ba Tê đã viết:
1/ Dữ liệu trong 2 cột J, K của bạn là Text không phải Date, phải chuyển thành đúng kiểu Date mới xét được.
2/ Bạn có chắc là mỗi người đều có 2 dòng cùng tên, ngày sinh?
Trong file này, tôi viết code theo 2 điều kiện trên, 2 cột J, K dòng nào trùng khoảng thời gian với dòng bên trên thì tô màu.
Nhấp chuột vào đây để mở rộng...
vâng ở file có cột phụ thì e cũng đã chuyển phân ngày thang sang kiểu date
sau khi e sort theo tên và ngày sinh thì code của bác cho ra 1.046 ô màu vàng.
Bài đã được tự động gộp:

@CHAOQUAY
Code lỗi out of range ở ngay dòng này bác ạ: j = Mang(0): k = Mang(1): x = CLng(Right(Mang(2), 2))
 
Lần chỉnh sửa cuối:
DarKLov3 đã viết:
vâng ở file có cột phụ thì e cũng đã chuyển phân ngày thang sang kiểu date
sau khi e sort theo tên và ngày sinh thì code của bác cho ra 1.046 ô màu vàng.
Bài đã được tự động gộp:

@CHAOQUAY
Code lỗi out of range ở ngay dòng này bác ạ: j = Mang(0): k = Mang(1): x = CLng(Right(Mang(2), 2))
Nhấp chuột vào đây để mở rộng...
Chủ yếu cần kết luận là code chạy đúng hay không, trường hợp nào sai, đâu cần biết bao nhiêu ô màu vàng.
 
DarKLov3 đã viết:
vâng ở file có cột phụ thì e cũng đã chuyển phân ngày thang sang kiểu date
sau khi e sort theo tên và ngày sinh thì code của bác cho ra 1.046 ô màu vàng.
Bài đã được tự động gộp:

@CHAOQUAY
Code lỗi out of range ở ngay dòng này bác ạ: j = Mang(0): k = Mang(1): x = CLng(Right(Mang(2), 2))
Nhấp chuột vào đây để mở rộng...
Bạn xem file này. Code chạy trên máy tôi bình thường. Nếu máy bạn chạy lỗi thì đành chịu bó tay.
Thân chào.
 

File đính kèm

Ba Tê đã viết:
Dữ liệu của bạn các dòng 64, 65, 66 (3 dòng cùng tên, ngày sinh), không đúng điều kiện 2/ của bài #6, do đó từ dòng 67 trở xuống sẽ có kết quả sai bét.
Nhấp chuột vào đây để mở rộng...
Hic. Cơ sở dữ liệu có trường hợp từ 3-4 người đều cùng tên cùng ngày sinh cơ bác ạ.
 
DarKLov3 đã viết:
Hic. Cơ sở dữ liệu có trường hợp từ 3-4 người đều cùng tên cùng ngày sinh cơ bác ạ.
Nhấp chuột vào đây để mở rộng...
Vậy sao xác định được 3, 4 người hay chỉ 1 người?
Xem tạm lại file này, Trùng tên trùng ngày sinh (sau khi sort sẽ coi như 1 người), chỉ xét dòng này và dòng liền kề bên trên.
Có 248 trường hợp trùng trong khoảng từ ngày, đến ngày.
 

File đính kèm

Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

C
  • Question Question
Xin hỏi Cách viết chú thích trong vba hiển thị tiếng việt (3 người xem)
Trả lời
3
Đọc
81
clien172
C
B
Xin giải pháp chống sửa, xóa dữ liệu đã nhập vào từ ngày hôm trước.
    • Thích
2
Trả lời
28
Đọc
7K
Hoàng Tuấn 868
H
G
Xin Code xóa Module.
Trả lời
8
Đọc
260
huhumalu
H
00029591
Cần hỗ trợ công thức tổng hợp dữ liệu không cần cột phụ Vlookup
Trả lời
3
Đọc
135
tle2003
T
D
  • Đã giải quyết
Quản lý email outlook qua VBA cần giúp đỡ
2
Trả lời
22
Đọc
528
nguyendang95
N
S
Mở 2 cửa sổ riêng biệt
2
Trả lời
30
Đọc
23K
sakuraluu
S
F
Nhập dấu . (chấm) ở bàn phím phụ không tự chuyển thành dấu , (phẩy)
Trả lời
16
Đọc
57K
tubaodn21
tubaodn21
PhanTuHuong
Sách Lập trình VBA trong AutoCAD đã có mặt
    • Thích
    • Yêu thích
2
Trả lời
21
Đọc
1K
PhanTuHuong
PhanTuHuong
L
Lỗi font khi convert từ pdf
Trả lời
3
Đọc
136
lamvu0677
L
N
Khắc phục lỗi "The picture is too large and will be truncated"
Trả lời
9
Đọc
5K
SonBDND
S
GPE-Trợ Lý
[GPE] TALKSHOW: PHÂN TÍCH BÁO CÁO ĐỘ NHẠY SỬ DỤNG EXCEL SOLVER (2 người xem)
    • Thích
Trả lời
0
Đọc
1K
GPE-Trợ Lý
GPE-Trợ Lý
K
  • Question Question
nhờ ace giúp đỡ tự động filter và In hàng loạt từng mục trong piviot table
Trả lời
1
Đọc
114
khkkh
K
K
Xin dax tính tỷ trọng doanh thu
Trả lời
18
Đọc
788
SteveNguyen1991
S
F
  • Question Question
Tự động Merge Mail bằng VBA
Trả lời
6
Đọc
222
Hoàng Tuấn 868
H
N
Một ví dụ về XLL add-in: Tương tác với Google Gemini dưới dạng công thức bảng tính
    • Thích
Trả lời
0
Đọc
141
nguyendang95
N
H
Nhờ anh/chị giúp đỡ em viết công thức trừ dữ liệu theo FiFo
Trả lời
1
Đọc
131
HieuCD
H
BNTT
LockXLS - Chương trình bảo vệ an toàn cho các file bảng tính Excel
    • Thích
4 5 6
Trả lời
118
Đọc
73K
YêuCN
Y
vsitcnh
Hỗ trợ công thức tính điểm dựa theo điều kiện.
Trả lời
14
Đọc
219
thuytrangbp28
T
micam111
  • Question Question
Cách chạy nhiều đồng hồ đếm giờ trong VBA
Trả lời
4
Đọc
707
thuytrangbp28
T
Emyeuexcel
  • Question Question
In tất cả các file trong cùng 1 thư mục
Trả lời
3
Đọc
227
Hoàng Tuấn 868
H
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
TALK SHOW: CÙNG NHẢY LAM BA ĐA VỚI HÀM LAMBDA (λ) EXCEL 365
Back
Top Bottom