Nhờ giúp đỡ lọc trùng lịch làm việc NV khoảng thời gian trong ngày

[ltnhanhcm]

Kính chào anh, chị, em có 1 file dữ liệu ca làm việc của NV khoảng 5000 dòng. Tuy nhiên file lịch làm việc đang có vấn đề là trong cùng 1 ngày làm việc 1 NV bị trùng thời gian với nhau, em muốn lọc dữ liệu trùng đó và cắt dòng trùng ra 1 sheet khác.
ví dụ: lịch làm việc NV A trong cùng 1 ngày 24/06 có 2 dòng
- dòng 1: ca 13h00_17h00
- dòng 2: ca 15h00_19h00
=> bị trùng thời gian từ 15h-17h -> em muốn lọc dữ liệu trùng này ra và cắt dòng trùng này sang 1 sheet khác.
Vì đây là dữ liệu để import lên web nên ca làm việc phải để đúng cú pháp như vậy.
Anh chị xem giúp em chú thích file đính kèm nhé. Em cảm ơn.

 

[Phuocam]

- dòng 1: ca 13h00_17h00
- dòng 2: ca 15h00_19h00
=> bị trùng thời gian từ 15h-17h -> em muốn lọc dữ liệu trùng này ra và cắt dòng trùng này sang 1 sheet khác.
Vì đây là dữ liệu để import lên web nên ca làm việc phải để đúng cú pháp như vậy.

.

Dùng thêm 2 cột phụ, tách 13h00_17h00 thành 13:00 và 17:00, rồi dùng Countifs để kiểm tra.

.

 

[ltnhanhcm]

.

Dùng thêm 2 cột phụ, tách 13h00_17h00 thành 13:00 và 17:00, rồi dùng Countifs để kiểm tra.

.

không được bác ơi, chỉ countif được nếu 1 NV 1 ngày trùng giờ giống nhau, còn trùng trong khung giờ thì ko lọc được.
giải pháp em nghĩ dùng VBA filter lịch 1 NV, sau đó lọc ra những ngày có 2 ca. rồi check trùng chỉ của NV đó nếu trùng thì cắt ra, xong rồi filter tiếp những NV khác y như vậy

 

[Phuocam]

không được bác ơi, chỉ countif được nếu 1 NV 1 ngày trùng giờ giống nhau, còn trùng trong khung giờ thì ko lọc được.
giải pháp em nghĩ dùng VBA filter lịch 1 NV, sau đó lọc ra những ngày có 2 ca. rồi check trùng chỉ của NV đó nếu trùng thì cắt ra, xong rồi filter tiếp những NV khác y như vậy

Dùng công thức cũng được.

.

 

[SA_DQ]

không được bác ơi, chỉ countif được nếu 1 NV 1 ngày trùng giờ giống nhau, còn trùng trong khung giờ thì ko lọc được.
giải pháp em nghĩ dùng VBA filter lịch 1 NV, sau đó lọc ra những ngày có 2 ca. rồi check trùng chỉ của NV đó nếu trùng thì cắt ra, xong rồi filter tiếp những NV khác y như vậy

Dữ liệu Trong các cột [Từ ngày] & [Tới ngày] của bạn chưa chuẩn
Cho nên phương án của bạn nêu ra chỉ có thể thực thi, khi các cột này ở dạng "MM/DD/yyyy"

 

[ltnhanhcm]

Dữ liệu Trong các cột [Từ ngày] & [Tới ngày] của bạn chưa chuẩn
Cho nên phương án của bạn nêu ra chỉ có thể thực thi, khi các cột này ở dạng "MM/DD/yyyy"

.

Dùng thêm 2 cột phụ, tách 13h00_17h00 thành 13:00 và 17:00, rồi dùng Countifs để kiểm tra.

.

Hiện tại em làm thủ công như sau:
Bước 1: text to column cột giờ và replace h = :, giờ thì chuyển thành số, ngày chuyển thành số
Bước 2: dùng countifs lọc NV trùng giờ giống nhau trong ngày ra 1 sheet
Bước 3: dùng countifs lọc NV có 2 ca giờ trong ngày ra 1 sheet và check trùng ở sheet này.
P/S: nếu NV 1 ngày 3 ca thì cũng phải lọc và tách ra 1 sheet khác để check trùng
Kết quả ra được rồi mà nhìn củ chuối lòng vòng quá, bác xem giúp em có công thức nào 1 phát ăn luôn không với cảm ơn 2 bác

 

[SA_DQ]

Hiện tại em làm thủ công như sau:
Bước 1: . . . . .
Bước 2: dùng countifs lọc NV trùng giờ giống nhau trong ngày ra 1 sheet
Bước 3: . . . . .

Ở bước 2 này chuyện đầu tiên là so sánh ngày giữa 1 dòng dữ liệu;
Nhưng trên máy của mình thì (2) cột ngày này lúc là chuỗi (15/4/2022) lúc thì 8/9/2022 thì chuyện so sánh trong VBA sẽ gặp nhiều khó khăn;
Mình nghỉ trước tiên là chuẩn hóa dữ liệu kiểu ngày, sau đó muốn làm gì cũng dễ;

[Trong những cột dữ liệu ngày-tháng-năm, người ta thường nhập theo dạng yyyy-mm-dd hay mm/dd/yyyy & sau đó định dạng theo kiểu dễ nhìn (dd/mm/yyyy)]
Còn bây chừ thì mình xin thua & tạm treo chủ đề này!

 

[ltnhanhcm]

Ở bước 2 này chuyện đầu tiên là so sánh ngày giữa 1 dòng dữ liệu;
Nhưng trên máy của mình thì (2) cột ngày này lúc là chuỗi (15/4/2022) lúc thì 8/9/2022 thì chuyện so sánh trong VBA sẽ gặp nhiều khó khăn;
Mình nghỉ trước tiên là chuẩn hóa dữ liệu kiểu ngày, sau đó muốn làm gì cũng dễ;

[Trong những cột dữ liệu ngày-tháng-năm, người ta thường nhập theo dạng yyyy-mm-dd hay mm/dd/yyyy & sau đó định dạng theo kiểu dễ nhìn (dd/mm/yyyy)]
Còn bây chừ thì mình xin thua & tạm treo chủ đề này!

Cột từ ngày đến ngày của mình là dạng text đúng dd/mm/yyyy mà nhỉ
Và mình làm lọc trùng ra được rồi giờ ghi macro với thêm code để auto thôi bác

 

[SA_DQ]

(Hàm Day())

Từ ngày

Tới ngày

6​

12/06/2023

12/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

17/06/2023

17/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

 

[ltnhanhcm]

(Hàm Day())

Từ ngày

Tới ngày

6​

12/06/2023

12/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

16/06/2023

16/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

16/06/2023

16/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

15/06/2023

15/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

Anh chuyển text sang date trước khi dùng hàm thử, do file này em dùng import lên web ngày phải là text mới import được

 

[Phuocam]

Hiện tại em làm thủ công như sau:
Bước 1: text to column cột giờ và replace h = :, giờ thì chuyển thành số, ngày chuyển thành số
Bước 2: dùng countifs lọc NV trùng giờ giống nhau trong ngày ra 1 sheet
Bước 3: dùng countifs lọc NV có 2 ca giờ trong ngày ra 1 sheet và check trùng ở sheet này.
P/S: nếu NV 1 ngày 3 ca thì cũng phải lọc và tách ra 1 sheet khác để check trùng
Kết quả ra được rồi mà nhìn củ chuối lòng vòng quá, bác xem giúp em có công thức nào 1 phát ăn luôn không với cảm ơn 2 bác

.
Chỉ cần 2 cột W và X, rồi dùng công thức:

=COUNTIFS($B$2:$B$6654,B2,$H$2:$H$6654,H2,$X$2:$X$6654,">="&W2,$W$2:$W$6654,"<="&X2)-1

Dòng nào kết quả lớn hơn 0 là trùng.

.

 

[ltnhanhcm]

.
Chỉ cần 2 cột W và X, rồi dùng công thức:

=COUNTIFS($B$2:$B$6654,B2,$H$2:$H$6654,H2,$X$2:$X$6654,">="&W2,$W$2:$W$6654,"<="&X2)-1

Dòng nào kết quả lớn hơn 0 là trùng.

.

Công thức chuẩn quá bác ơi, lọc phát ra hết luôn, cảm ơn bác nha. Giờ em làm tiếp ghi macro lại rồi thêm code cắt đoạn trùng ra sheet khác.

Bài đã được tự động gộp: 24/6/23

(Hàm Day())

Từ ngày

Tới ngày

6​

12/06/2023

12/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

16/06/2023

16/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

16/06/2023

16/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

15/06/2023

15/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

Máy nhà em dùng là office 360, em dùng hàm ở cột ngày nó cho ra kết quả hết xong em mở file ở 1 máy khác dùng excel 2016 thì 1 số dòng nó là VALUE!, nên em nghĩ do version excel ấy anh

Bài đã được tự động gộp: 24/6/23

(Hàm Day())

Từ ngày

Tới ngày

6​

12/06/2023

12/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

16/06/2023

16/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

16/06/2023

16/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

15/06/2023

15/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

#VALUE!​

14/06/2023

14/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

17/06/2023

17/06/2023

#VALUE!​

15/06/2023

15/06/2023

#VALUE!​

16/06/2023

16/06/2023

#VALUE!​

18/06/2023

18/06/2023

6​

12/06/2023

12/06/2023

#VALUE!​

13/06/2023

13/06/2023

Rồi em biết rồi, máy nhà anh đang set ngày của máy là mm/dd/yyyy nên anh dùng hàm nó lỗi đó, anh thấy hàm anh lỗi các dòng ngày >12 không, do máy nó đang lấy mm trước nên dòng nào >12 là >12 tháng nó ra lỗi liền. Anh đổi sang dd/mm/yyyy đi em đảm bảo dùng hàm ok liền

 

[Thóc Sama]

Công thức chuẩn quá bác ơi, lọc phát ra hết luôn, cảm ơn bác nha. Giờ em làm tiếp ghi macro lại rồi thêm code cắt đoạn trùng ra sheet khác.

Bài đã được tự động gộp: 24/6/23

Máy nhà em dùng là office 360, em dùng hàm ở cột ngày nó cho ra kết quả hết xong em mở file ở 1 máy khác dùng excel 2016 thì 1 số dòng nó là VALUE!, nên em nghĩ do version excel ấy anh

Bài đã được tự động gộp: 24/6/23

Rồi em biết rồi, máy nhà anh đang set ngày của máy là mm/dd/yyyy nên anh dùng hàm nó lỗi đó, anh thấy hàm anh lỗi các dòng ngày >12 không, do máy nó đang lấy mm trước nên dòng nào >12 là >12 tháng nó ra lỗi liền. Anh đổi sang dd/mm/yyyy đi em đảm bảo dùng hàm ok liền

“Anh ấy” không dùng office 360 mà dùng office 365 bạn à.

 

[ltnhanhcm]

“Anh ấy” không dùng office 360 mà dùng office 365 bạn à.

Em phát hiện ra rồi ko phải do version excel mà do định dạng ngày của máy bác ấy là mm/dd/yyyy nên công thức ra lỗi đó

 

[SA_DQ]

Rồi em biết rồi, máy nhà anh đang set ngày của máy là mm/dd/yyyy nên anh dùng hàm nó lỗi đó, anh thấy hàm anh lỗi các dòng ngày >12 không, do máy nó đang lấy mm trước nên dòng nào >12 là >12 tháng nó ra lỗi liền. Anh đổi sang dd/mm/yyyy đi em đảm bảo dùng hàm ok liền

Chả lẽ mình phải đổi định dạng mặc định sang DD/MM/yyyy để giúp bạn?
(/ậy mình ngưng ở đây & không thể . . . . tiếp tục với file của bạn

 

[VetMini]

...
giải pháp em nghĩ dùng VBA filter lịch 1 NV, sau đó lọc ra những ngày có 2 ca. rồi check trùng chỉ của NV đó nếu trùng thì cắt ra, xong rồi filter tiếp những NV khác y như vậy

Bạn thử viết code cho giải pháp của bạn chưa?

..., bác xem giúp em có công thức nào 1 phát ăn luôn không với cảm ơn 2 bác

Ở GPE này hiếm có chuyện "1 phát ăn luôn".
Đối với bạn là muốn "bấm nút, chạy từ a đến z"
Đối với người viết code giúp bạn là "đưa code lên, thớt sẽ cho biết những điều cần chỉnh sửa". Tay nào viết được "1 phát ăn luôn" là có thể đi làm nghề thầy bói.

 

[ltnhanhcm]

Bạn thử viết code cho giải pháp của bạn chưa?


Ở GPE này hiếm có chuyện "1 phát ăn luôn".
Đối với bạn là muốn "bấm nút, chạy từ a đến z"
Đối với người viết code giúp bạn là "đưa code lên, thớt sẽ cho biết những điều cần chỉnh sửa". Tay nào viết được "1 phát ăn luôn" là có thể đi làm nghề thầy bói.

Đã code xong rồi anh.

 

[ltnhanhcm]

Chả lẽ mình phải đổi định dạng mặc định sang DD/MM/yyyy để giúp bạn?
(/ậy mình ngưng ở đây & không thể . . . . tiếp tục với file của bạn

Thôi anh không biết làm thì cứ nhận, bày đặt nói file tôi sai định dạng. Đến cái hàm day chạy còn sai không biết sai chỗ nào thì quá chán.
Bài này tôi làm 30 phút là xong rồi, anh xem mà tham khảo sau này có ai nhờ thì biết mà còn chỉ, chứ không giúp được mà cứ chém gió thì vào group Thư Giãn mà sinh hoạt đừng bon chen vô đây.

 

[bebo021999]

Thôi anh không biết làm thì cứ nhận, bày đặt nói file tôi sai định dạng. Đến cái hàm day chạy còn sai không biết sai chỗ nào thì quá chán.
Bài này tôi làm 30 phút là xong rồi, anh xem mà tham khảo sau này có ai nhờ thì biết mà còn chỉ, chứ không giúp được mà cứ chém gió thì vào group Thư Giãn mà sinh hoạt đừng bon chen vô đây.

Dù sao đi nữa thì bạn cũng nên tỏ thái độ tôn trọng với bác SA-DQ (Thành viên lớn tuổi nhất GPE, có lẽ sắp có cháu cố rồi cũng nên).
Quay lại với đề tài của bạn, cách của tôi là duplicate sheet, tạo sheet "trùng", dùng 3 cột cuối để tách giờ, và lồng hàm COUNTIFS
Sau đó xóa hết các cột và dòng thừa.

Option Explicit
Sub Trung()
Dim lr&, i&, h1, h2, rng, res()
On Error Resume Next
Sheets("Trung").Delete
On Error GoTo 0
Sheets("Sheet1").Copy after:=Sheets("Sheet1")
ActiveSheet.Name = "Trung"
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A2:V" & lr).Value
ReDim res(1 To UBound(rng), 1 To 3)
For i = 1 To UBound(rng)
    h1 = Split(rng(i, 10), "_")(0): h2 = Split(rng(i, 10), "_")(1)
    res(i, 1) = rng(i, 2)
    res(i, 2) = CDate(rng(i, 8)) + TimeSerial(Left(h1, Len(h1) - 3), Right(h1, 2), 0)
    res(i, 3) = CDate(rng(i, 9)) + TimeSerial(Left(h2, Len(h2) - 3), Right(h2, 2), 0)
Next
Range("W2").Resize(UBound(res), 3).Value = res
With Range("Z2:Z" & lr)
    .Formula = "=1/(COUNTIFS($W$2:$W" & lr & ",W2,$X$2:$X" & lr & ",""<="" & X2,$Y$2:$Y" & lr & ","">="" & X2)+COUNTIFS($W$2:$W" & lr & ",W2,$X$2:$X" & lr & ",""<="" & Y2,$Y$2:$Y" & lr & ","">="" & Y2)>2)"
    .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
End With
Range("W:Z").Delete
Range("A1").Select
End Sub

 

[SA_DQ]

Dù sao đi nữa thì bạn cũng nên tỏ thái độ tôn trọng với bác SA-DQ (Thành viên lớn tuổi. . . . .

BeBo lại làm chuyện dã tràng rồi.
& coi chừng bị ăn chưỡi nữa bây chừ!