Nhờ hỗ trợ lọc duy nhất và cập nhật thông tin mới nhất theo thời gian (1 người xem)

[hongquang150888]

Kính gửi các bác,

Em có 1 file gồm 2 Sheet tên DATA và STATUS (đính kèm)
Toàn bộ dữ liệu sẽ được nhập lên sheet Data
Ở Sheet STATUS, Em muốn cột A (IMES) sẽ hiện toàn bộ các IMES trên sheet Data (lược bỏ những IMES bị trùng), từ đó, các thông tin tại các cột B (STATUS), C(A/D), D (When), E (Where)... Sẽ được cập nhập thông tin mới nhất theo thời gian.

Hiện tại em đang dùng hàm =Max(if(.....)) nhưng nó rất nặng vì sheet DATA rất lớn. Các bác có thể bớt chút thời gian viết giúp em một đoạn VBA cho file này được hok ạ.
Chân thành cảm ơn các bác

 

[SA_DQ]

Bạn thử với macro sự kiện này ở trang tính bạn cần danh sách duy nhất xuất hiện:

Private Sub Worksheet_Activate()
Dim Dict As Object, Arr() As Variant, sArr()
Dim Rws As Long, J As Long, W As Integer, Dm As Integer

  Set Dict = CreateObject("Scripting.Dictionary")
  [B2].CurrentRegion.Offset(1).ClearContents
  With Sheets("Data")
        Rws = .[C2].CurrentRegion.Rows.Count
        sArr() = .[C2].Resize(Rws, 12).Value
        ReDim Arr(1 To UBound(sArr()), 1 To 11)
        For J = 1 To UBound(sArr())
            If Not IsEmpty(sArr(J, 1)) And Not Dict.exists(sArr(J, 1)) Then
                W = W + 1:                                      Dict.Add sArr(J, 1), W
                Arr(W, 1) = sArr(J, 1)
                For Dm = 3 To 12
                    Arr(W, Dm - 1) = sArr(J, Dm)
                Next Dm
            End If
        Next J
        If W Then
            [A2].Resize(W, 11).Value = Arr()
        End If
  End With
End Sub

 

[Ba Tê]

Ở Sheet STATUS, Em muốn cột A (IMES) sẽ hiện toàn bộ các IMES trên sheet Data (lược bỏ những IMES bị trùng), từ đó, các thông tin tại các cột B (STATUS), C(A/D), D (When), E (Where)... Sẽ được cập nhập thông tin mới nhất theo thời gian.

Bạn thử với macro sự kiện này ở trang tính bạn cần danh sách duy nhất xuất hiện:

1/ Hình như code chưa đúng yêu cầu phần tô đậm?
2/ Data hình như đã xếp thứ tự tăng dần cột G (When)?
Nếu 2/ là đúng thì
- Code trên quét ngược từ dưới lên, thằng nào chưa có mặt thì "hốt", có rồi bỏ qua.
- Quét từ trên xuống, phải thay thế bằng tất cả các cột dữ liệu mới của từng Imei S

 

[hongquang150888]

Bạn thử với macro sự kiện này ở trang tính bạn cần danh sách duy nhất xuất hiện:

Private Sub Worksheet_Activate()
Dim Dict As Object, Arr() As Variant, sArr()
Dim Rws As Long, J As Long, W As Integer, Dm As Integer

  Set Dict = CreateObject("Scripting.Dictionary")
  [B2].CurrentRegion.Offset(1).ClearContents
  With Sheets("Data")
        Rws = .[C2].CurrentRegion.Rows.Count
        sArr() = .[C2].Resize(Rws, 12).Value
        ReDim Arr(1 To UBound(sArr()), 1 To 11)
        For J = 1 To UBound(sArr())
            If Not IsEmpty(sArr(J, 1)) And Not Dict.exists(sArr(J, 1)) Then
                W = W + 1:                                      Dict.Add sArr(J, 1), W
                Arr(W, 1) = sArr(J, 1)
                For Dm = 3 To 12
                    Arr(W, Dm - 1) = sArr(J, Dm)
                Next Dm
            End If
        Next J
        If W Then
            [A2].Resize(W, 11).Value = Arr()
        End If
  End With
End Sub

Wow. Em cảm ơn bác SA_DQ nhiều, code VBA của Bác quá chuẩn luôn, nhưng em không hiểu sao khi copy code của Bác vào file chính của em (file em gửi là file lược bỏ bởi vì nó rất nặng), thì nó chay không được chính xác lắm (cụ thể những trường thông tin phía dưới không được cập nhập). Em đã đổi Sheets ("Data") trong code của Bác rồi, bác xem em còn cần phải chỉnh gì nữa không ạ. Mong bác giúp em với nhé.

 

[hongquang150888]

1/ Hình như code chưa đúng yêu cầu phần tô đậm?
2/ Data hình như đã xếp thứ tự tăng dần cột G (When)?
Nếu 2/ là đúng thì
- Code trên quét ngược từ dưới lên, thằng nào chưa có mặt thì "hốt", có rồi bỏ qua.
- Quét từ trên xuống, phải thay thế bằng tất cả các cột dữ liệu mới của từng Imei S

Dạ. Em cảm ơn Bác.
2/ Data hình như đã xếp thứ tự tăng dần cột G (When)?
Cái này phần đầu em nhập thì hơi nhập nhằng, nên cột G (When) không được sắp xếp theo thứ tự, bây giờ Data tốt hơn rồi, nên nếu bác chạy hàm đó thì em sẽ fillter lại đáp ứng đúng "đầu vào" ạ.
Bác có thể hỗ trợ em chỉnh lại 1 chút trong code của bác SA_DQ hok anh, bác SA_DQ làm code đó, em chạy trong file Help đó rất okay, nhưng khi cho chạy vào file chính thì nó không cập nhập theo ý muốn bác ak. Em cảm ơn bác nhiều nhé.

 

[befaint]

Sau khi được sự trợ giúp từ người thân ta có đoạn này.

Option Explicit

Sub vidu_group_by()
    Dim cn As Object, rs As Object, sql As String, lR As Long
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
  
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
                ";Extended Properties=""Excel 12.0;HDR=No;"""
  
    With Sheet1
        lR = .Range("D" & Rows.Count).End(xlUp).Row
        If lR = 1 Then Exit Sub
        sql = "select t1.* from [" & .Name & "$D2:N" & lR & "] as t1 " & _
              "inner join (select f1 as imei , max(f4) as maxdate from [" & .Name & "$D2:N" & lR & "] group by f1 having count(*)=1) as t2 " & _
              "on t1.f1 = t2.imei and t1.f4 = t2.maxdate order by t1.f4 asc"
        'Debug.Print sql
    End With
  
    With Sheet2
        .Range("A2:K1048500").ClearContents
        rs.Open (sql), cn, 1

        If Not rs.EOF Then .Range("A2").CopyFromRecordset rs
    End With
    rs.Close: cn.Close
    Set rs = Nothing: Set cn = Nothing

End Sub

 

[hongquang150888]

Sau khi được sự trợ giúp từ người thân ta có đoạn này.

Option Explicit

Sub vidu_group_by()
    Dim cn As Object, rs As Object, sql As String, lR As Long
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
  
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
                ";Extended Properties=""Excel 12.0;HDR=No;"""
  
    With Sheet1
        lR = .Range("D" & Rows.Count).End(xlUp).Row
        If lR = 1 Then Exit Sub
        sql = "select t1.* from [" & .Name & "$D2:N" & lR & "] as t1 " & _
              "inner join (select f1 as imei , max(f4) as maxdate from [" & .Name & "$D2:N" & lR & "] group by f1) as t2 " & _
              "on t1.f1 = t2.imei and t1.f4 = t2.maxdate order by t1.f4 asc"
        'Debug.Print sql
    End With
  
    With Sheet2
        .Range("A2:K1048500").ClearContents
        rs.Open (sql), cn, 1

        If Not rs.EOF Then .Range("A2").CopyFromRecordset rs
    End With
    rs.Close: cn.Close
    Set rs = Nothing: Set cn = Nothing

End Sub

Wow. Em cảm ơn anh befaint, hàm của anh tuyệt vời luôn. Nhưng bên em bị dính 1 lỗi anh ak. Anh có thể giúp em được hok ạ.
Theo đó, nếu 1 mã IMES có 2 hàng trong 1 ngày thì kết quả trả về là cái mã IMES đó tồn tại 2 hàng luôn. Phiền anh giúp em thêm một chút, và loại bỏ để đảm bảo chỉ có 1 mã IMES duy nhất hok anh.
Em cảm ơn anh nhiều ạ.

 

[befaint]

giúp em được hok

Dạ, em mí 18 tủi thui ak.
"duy nhất mà có 2 hàng" thì cô giáo em chưa dạy ạ.

 

[hongquang150888]

Dạ, em mí 18 tủi thui ak.
"duy nhất mà có 2 hàng" thì cô giáo em chưa dạy ạ.

Hiện tại hàm VBA của bạn đang viết, khi mình đưa vào file dữ liệu thì xuất hiện tình trạng có 2 hàng vs mã IMES giống nhau. Đó là do trong cùng 1 ngày, người nhập họ nhập 2 trạng thái. VD IMES BTK000121, trong cùng 1 ngày 10/5 người nhập họ nhập là Nhập kho (Store) và Sold (bán) thì khi chạy VBA của bạn nó hiện ra cả 2 trạng thái đó luôn. Ý mình là bạn có cách nào để giải quyết thêm vấn đề này không ấy mà. Dạng như chỉ lấy cái trạng thái cuối cùng thôi (IMES BTK000121 chỉ Sold thôi ak)
Bạn giúp mình với nhé.
P/S. Sorry khi chưa xem thông tin của bạn trước khi xưng hô. hihihi

 

[befaint]

giải quyết thêm vấn đề này

Em vừa mí hỏi cô giáo rồi a ka.. và chỉnh lại ở bài trên, anh thử lại a ka.

 

[hongquang150888]

Em vừa mí hỏi cô giáo rồi a ka.. và chỉnh lại ở bài trên, anh thử lại a ka.

Cảm ơn bạn nhiều nhen.
Mình thấy VBA lúc đầu của bạn chạy tốt ghê luôn. Chỉ bị có 1 vấn đề đó là trung IMES.
Cái thứ 2 bạn chỉnh lại, mình thấy nó bị sao ak, mất nhiều dữ liệu bên mình lắm, nên mình đang phải sửa lại data để không cho phép nhập 2 hoạt động cùng 1 ngày.
Thanks bạn nhiều heng.

 

[befaint]

nguyên nhân tại sao

Dạ, em mí hỏi ông Bill Gate, ổng kêu là "ông chưa làm tính năng này cháu a ka". Anh thông cảm cho ổng ấy a ka 47.

 

[hongquang150888]

Dạ, em mí hỏi ông Bill Gate, ổng kêu là "ông chưa làm tính năng này cháu a ka". Anh thông cảm cho ổng ấy a ka 47.

heheheeh. CÁi đó mình làm được rồi, do mình làm sai dữ liệu đầu vào thôi.
Thanks bạn nhé.

 

[hongquang150888]

Em vừa mí hỏi cô giáo rồi a ka.. và chỉnh lại ở bài trên, anh thử lại a ka.

Bạn ơi, bạn giúp mình chỉnh lại cái code VBA được hok, file bạn chỉnh khi mình chạy nó không hiện ra đủ dữ liệu bạn ak.
Giúp mình với nhen. Thanks bạn nhiều nhiều

 

[ongke0711]

Em vừa mí hỏi cô giáo rồi a ka.. và chỉnh lại ở bài trên, anh thử lại a ka.

@Bạn befaint 18+ : dùng Group By theo 2 cột cách cũ đã ra các giá trị không trùng rồi, nếu thêm count(*) sẽ mất dữ liệu. Khi câu móc dữ liệu với Data (t1) thì phải loại bỏ trùng ở vế Data. Nếu chủ thớt đảm bảo sau này không có trùng thì code cũ chạy ngon.
@chủ thớt: nếu loại bỏ lỗi do nhập liệu, thực tế có phát sinh trùng 2, 3 lần cùng một Imei trong cùng 1 ngày không?

 

[hongquang150888]

@Bạn befaint 18+ : dùng Group By theo 2 cột cách cũ đã ra các giá trị không trùng rồi, nếu thêm count(*) sẽ mất dữ liệu. Khi câu móc dữ liệu với Data (t1) thì phải loại bỏ trùng ở vế Data. Nếu chủ thớt đảm bảo sau này không có trùng thì code cũ chạy ngon.
@chủ thớt: nếu loại bỏ lỗi do nhập liệu, thực tế có phát sinh trùng 2, 3 lần cùng một Imei trong cùng 1 ngày không?

Dạ, đúng như anh nói, code cũ của bạn @befaint chạy rất okay, chỉ tiếc là khi em đưa vào data chính bên em thì bị dính lỗi đó.
Trường hợp trong 1 ngày, sẽ có nhiều dòng cho 1 mã IMES vì 1 sản phẩm có thể vừa nhập kho, và bán ngay trong 1 ngày. Hiện tại em đang phải "đổi ngày" để tránh điều đó, nhưng nếu bác có cách nào mà cho phép cả việc nhập liệu thì tuyệt vời ạ.

 

[ongke0711]

Trường hợp trong 1 ngày, sẽ có nhiều dòng cho 1 mã IMES vì 1 sản phẩm có thể vừa nhập kho, và bán ngay trong 1 ngày. Hiện tại em đang phải "đổi ngày" để tránh điều đó, nhưng nếu bác có cách nào mà cho phép cả việc nhập liệu thì tuyệt vời ạ.

Nếu vậy thực tế nghiệp vụ là có trường hợp: nhiều dòng cùng 1 IMES cùng 1 ngày.
Vậy khi nhập liệu, để được chuẩn hoá bạn phải có một cột phụ nào đó để phân biệt dòng này với dòng khác (cùng 1 IMES, cùng này), và cột phụ này bắt buộc phải có dữ liệu chứ không được để trống. ví dụ: thêm cột [NghiẹpVu]: nhập kho; xuất bán; xuất hoàn trả... Khi đó sẽ có đủ dữ liệu để lấy dữ liệu không bị trùng.

 

[hongquang150888]

Nếu vậy thực tế nghiệp vụ là có trường hợp: nhiều dòng cùng 1 IMES cùng 1 ngày.
Vậy khi nhập liệu, để được chuẩn hoá bạn phải có một cột phụ nào đó để phân biệt dòng này với dòng khác (cùng 1 IMES, cùng này), và cột phụ này bắt buộc phải có dữ liệu chứ không được để trống. ví dụ: thêm cột [NghiẹpVu]: nhập kho; xuất bán; xuất hoàn trả... Khi đó sẽ có đủ dữ liệu để lấy dữ liệu không bị trùng.

Dạ. Vậy em thêm 1 cột Hour. trước cột When, khi đó, nếu trong ngày đó họ nhập 1 IMES nhiều lần, khi đó người nhập sẽ nhập cả giờ và ngày thì các anh có thể viết giúp em dựa vào thời gian cuối cùng để lấy dữ liệu được hok anh?

 

[ongke0711]

Dạ. Vậy em thêm 1 cột Hour. trước cột When, khi đó, nếu trong ngày đó họ nhập 1 IMES nhiều lần, khi đó người nhập sẽ nhập cả giờ và ngày thì các anh có thể viết giúp em dựa vào thời gian cuối cùng để lấy dữ liệu được hok anh?

- Ưu tiên nên thiết kế (thêm) cột có ý nghĩa, đóng góp trong việc tổng hợp phân tích số liệu (như ví dụ thêm cột nghiệp vụ tôi đã để câp, chỉ ví dụ, phải tuỳ theo CSDL của bạn) tránh dữ liệu dư thừa, CSDL phình lên một cách lãng phí.
- Nếu không thêm được các cột để phân biệt dòng thì dùng cột phụ [Hour] như bạn đề cập cũng được (chỉ có giá trị trong code tổng hơp dữ liệu). Những cột phụ dạng này thì không nên phiền người dùng nhập liệu mà viết code cho hệ thống tự cập nhật dữ liệu.
CSDL hện tại của bạn tôi thấy chỉ có mã "BTK000106" laf có vẻ bị trùng do lỗi nhập liệu, còn các dòng còn lại đều có thể phân biệt dòng thông qua 2 cột [When] và [Status].



Vậy có thể dùng cột [Status] này để phân biệt các dòng. Khi nhập liệu bắt buộc phải có dữ liệu cho cột này trước khi lưu xuống Worksheet.
Bạn thấy sao? Có khi nào cột này cũng bị trùng không? cùng 1 IMES - 1 ngày - 1 Status. Nếu vẫn trùng thì dùng cột [Hour] thôi.
Nói thêm là tôi có thấy cột [A/D], nếu để code chạy nhanh thì có thể chỉ chọn những dòng nào còn "Active" để xử lý, dòng nào Deactivated thì bỏ qua khỏi xử lý đỡ tốn tài nguyên.

 

[hongquang150888]

- Ưu tiên nên thiết kế (thêm) cột có ý nghĩa, đóng góp trong việc tổng hợp phân tích số liệu (như ví dụ thêm cột nghiệp vụ tôi đã để câp, chỉ ví dụ, phải tuỳ theo CSDL của bạn) tránh dữ liệu dư thừa, CSDL phình lên một cách lãng phí.
- Nếu không thêm được các cột để phân biệt dòng thì dùng cột phụ [Hour] như bạn đề cập cũng được (chỉ có giá trị trong code tổng hơp dữ liệu). Những cột phụ dạng này thì không nên phiền người dùng nhập liệu mà viết code cho hệ thống tự cập nhật dữ liệu.
CSDL hện tại của bạn tôi thấy chỉ có mã "BTK000106" laf có vẻ bị trùng do lỗi nhập liệu, còn các dòng còn lại đều có thể phân biệt dòng thông qua 2 cột [When] và [Status].

View attachment 227726

Vậy có thể dùng cột [Status] này để phân biệt các dòng. Khi nhập liệu bắt buộc phải có dữ liệu cho cột này trước khi lưu xuống Worksheet.
Bạn thấy sao? Có khi nào cột này cũng bị trùng không? cùng 1 IMES - 1 ngày - 1 Status. Nếu vẫn trùng thì dùng cột [Hour] thôi.
Nói thêm là tôi có thấy cột [A/D], nếu để code chạy nhanh thì có thể chỉ chọn những dòng nào còn "Active" để xử lý, dòng nào Deactivated thì bỏ qua khỏi xử lý đỡ tốn tài nguyên.

Chào anh @ongke0711
Em đã thêm cột Hour trong file điính kèm, phiền anh em trong diễn đàn dành chút thời gian giúp em chỉnh code VBA của bạn @befaint để lấy dữ liệu theo sheet 2 như trên nhé.
@befaint ơi, cảm ơn bạn nhiều nhé.! nếu được bạn giúp mình 1 chút nhen. Mình có thêm 1 cột trong sheet 2 tên "Date Sold", bạn có cách nào xuất giúp miình cái ngày ĐẦU TIÊN mà IMES đó ghi nhận là "Sold" trong cột STATUS không bạn. Nó hơi ngược với các dòng khác một chút. Các cột khác là lấy dữ liệu mới nhất nhưng riêng cột Date Sold thì lấy dữ liệu cũ nhất nếu IMES đó ghi nhận là "Sold". Vì ngày đó mình sẽ lấy là ngày để Bảo hành sản phẩm đó bạn.

Cảm ơn bạn nhiều nhé, nếu anh em trong group ở Nha Trang, xin cho mình được mời đi uống bia 1 lần nhen.! Thanks all.