Dò tìm chuỗi trong chuỗi (1 người xem)

[voda]

Nhờ các bạn dò tìm các ô trong cột A sheet1, giá trị của các ô trong cột A sheet2. Nếu có, ghi giá trị mã nhân viên tương ứng vào cột B sheet1. ( Xem file đính kèm)

Đó là câu hỏi của bạn Braverynew nhờ mình post lên diễn đàn. Mong được các bạn giúp đỡ.

 

[ptm0412]

Có vẻ như dữ liệu trích ra từ phần mềm nên khá đồng nhất về tên Quận.
Phương thức find có lẽ sẽ bị lẫn lộn nếu số nhà địa chỉ gần giống nhau.
Tôi nghĩ dùng instr() là thích hợp nhất.

Sub timma()
Dim cll1 As Range, cll2 As Range
endR2 = Sheet2.[a500].End(xlUp).Row
endR1 = Sheet1.[a50000].End(xlUp).Row
For Each cll1 In Sheet1.Range("A2:A" & endR1)
    For Each cll2 In Sheet2.Range("A2:A" & endR2)
        If InStr(1, cll1, cll2) > 0 Then
            cll1.Offset(0, 1) = cll2.Offset(0, 1): Exit For
        End If
    Next
Next
End Sub

 

[ndu96081631]

Đó là câu hỏi của bạn Braverynew nhờ mình post lên diễn đàn. Mong được các bạn giúp đỡ.

Đúng ra bài này có thể làm bằng công thức, nhưng nếu dử liệu nhiều thì file sẽ khá năng và chậm!
Tôi làm giúp bạn bằng cách tạo 1 hàm người dùng!
Code:

Function StLookup(LVal As String, LArray As Range, Optional ColIndex As Byte = 1)
  Dim Clls As Range
  For Each Clls In LArray.Resize(, 1)
    StLookup = IIf(UCase$(LVal) Like "*" & UCase$(Clls) & "*", Clls(, ColIndex), "")
    If Len(StLookup) Then Exit For
  Next Clls
End Function

Xem file
(bạn đặt tên file là Helpme nghe... khiếp quá ---> Làm liên tưởng đến virus)

 

[ndu96081631]

Ah... nghe sư phụ ptm0412 nhắc mới để ý... thì ra có sự trùng lắp trong quá trình dò tìm
Vậy em sửa lại tí:

Function StLookup(LVal As String, LArray As Range, Optional ColIndex As Byte = 1)
  Dim Clls As Range, LenSt As Long
  For Each Clls In LArray.Resize(, 1)
    If UCase$(LVal) Like "*" & UCase$(Clls) & "*" Then
      If Len(Clls.Value) > LenSt Then
        LenSt = Len(Clls.Value)
        StLookup = Clls(, ColIndex)
      End If
    End If
  Next Clls
End Function

Hình như Sub của sư phụ chạy vẫn chưa chính xác

 

[ptm0412]

Chích xác chứ, instr() đi tìm từ đầu chuỗi đến cuối chuỗi, nghĩa là tìm phường trước, Quận sau. Cấu trúc dữ liệu của sheet 2 cũng vậy.
Thí dụ P Thanh Lương (dòng 56 False) là của mã NV C1_BINHNQ_MTS là dòng 36 của sheet2.
Trong khi đó function dùng like nó tìm cách nào đó mà nó bỏ qua P Thanh Lương, lụm Q. Hai Bà Trưng!

Tương tự là P Bạch Đằng, ...

Ghi chú: Đoạn code khi nãy post lên có thiếu sót câu lệnh Exit for sau khi tìm thấy, đã bổ sung.

 

[voda]

Nhìn vào dữ liệu được trích rút từ mấy cái phần mềm, mắt già cứ hoa lên không biết đâu là đúng, đâu là sai. Nhờ các bạn coi lại dùm, 2 đoạn code cho ra kết quả khác nhau ở một số ô.

 

[ndu96081631]

Chích xác chứ, instr() đi tìm từ đầu chuỗi đến cuối chuỗi, nghĩa là tìm phường trước, Quận sau. Cấu trúc dữ liệu của sheet 2 cũng vậy.
Thí dụ P Thanh Lương (dòng 56 False) là của mã NV C1_BINHNQ_MTS là dòng 36 của sheet2.
Trong khi đó function dùng like nó tìm cách nào đó mà nó bỏ qua P Thanh Lương, lụm Q. Hai Bà Trưng!

Tương tự là P Bạch Đằng, ...

Ghi chú: Đoạn code khi nãy post lên có thiếu sót câu lệnh Exit for sau khi tìm thấy, đã bổ sung.

Sư phụ ơi! Sub của sư phụ, tại dòng 56 nó ra C1_HAINT_MTS cơ mà ---> Kết quả UDF của em là C1_BINHNQ_MTS ---> Kết quả này mới đúng chứ
Và sau khi sư phụ bổ sung (còn em quay về code củ) thì 2 kết quả giống nhau
Ẹc... Ẹc...
--------------------
Ah.... em nghĩ kỹ rồi: Có lẽ InStr mới là chính xác nhất!

 

[ptm0412]

Nhờ các bạn coi lại dùm, 2 đoạn code cho ra kết quả khác nhau ở một số ô.

Dạ đúng rồi, code của em thiếu dòng Exit for, nên đã tìm thấy P Bưởi mà chưa chịu dừng, lại chạy xuống tìm thấy Q. Tây Hồ nữa, nên nó thay thế mã đúng bằng mã sai.

Có lẽ InStr mới là chính xác nhất!

Instr() trong trường hợp này thì đúng, nhưng khổ cái là nếu cái anh chàng phụ trách "Q. hai Bà trưng" mà đứng trên cái anh phụ trách "P.Thanh Lương" cũng Q.Hai Bà Trưng, thì anh đầu phụ trách Quận nhận hết việc, mấy anh phụ trách Phường chơi không.

 

[ndu96081631]

Dạ đúng rồi, code của em thiếu dòng Exit for, nên đã tìm thấy P Bưởi mà chưa chịu dừng, lại chạy xuống tìm thấy Q. Tây Hồ nữa, nên nó thay thế mã đúng bằng mã sai.


Instr() trong trường hợp này thì đúng, nhưng khổ cái là nếu cái anh chàng phụ trách "Q. hai Bà trưng" mà đứng trên cái anh phụ trách "P.Thanh Lương" cũng Q.Hai Bà Trưng, thì anh đầu phụ trách Quận nhận hết việc, mấy anh phụ trách Phường chơi không.

Em còn 1 thắc mắc (chắc tác giả phải làm rõ)
- Dòng 28: 616 Đường Bưởi P.Bưởi Q.Tây Hồ Hà Nội ---> Ưu tiên lấy cái nào đây: Q. Tây hồ với mã C1_KIENND_MTS hay P.Bưởi Q.Tây Hồ với mã C1_BINHHT_MTS_XM
- Nếu lấy theo kết quả "dài" nhất thì lúc đó phải xem lại, có thể InStr sẽ không còn chính xác mà phải dùng LEN

 

[ptm0412]

Có lẽ ưu tiên phường, vì mình suy luận thế này:
1 Quận có giả sử 10 phường, 7 phường giàu có người phụ trách riêng, 3 phường còn lại nghèo, cho 1 anh phụ trách hết.

Đùa thôi, nhưng đây chắc là dữ liệu của 1 chi cục bưu điện, có người phụ trách phường nhiều thư, và những phường ít thư hoặc phường nhỏ gần nhau gom lại cho 1 người phụ trách.

 

[braverynew]

Có lẽ ưu tiên phường, vì mình suy luận thế này:
1 Quận có giả sử 10 phường, 7 phường giàu có người phụ trách riêng, 3 phường còn lại nghèo, cho 1 anh phụ trách hết.

Đùa thôi, nhưng đây chắc là dữ liệu của 1 chi cục bưu điện, có người phụ trách phường nhiều thư, và những phường ít thư hoặc phường nhỏ gần nhau gom lại cho 1 người phụ trách.

Em xin cảm ơn các bác đặc biệt là sư phụ ptm0412. Em cũng đã sử dụng được và hiểu hơn về Macro trong excel. Cám ơn voda đã giúp mình đưa câu hỏi lên trên diễn đàn với cách hiểu của chuyên gia excel. Hôm trước mình có hỏi sư phụ ptm0412 mà mãi bác ý mới hiểu được câu hỏi vì em hỏi hơi "cà rốt" ạ. Một lần nữa xin cảm ơn các bác. Nếu có dịp cho em được giao lưu và hậu tạ các bác chầu cafe. Em ở HN.

 

[braverynew]

Em còn 1 thắc mắc (chắc tác giả phải làm rõ)
- Dòng 28: 616 Đường Bưởi P.Bưởi Q.Tây Hồ Hà Nội ---> Ưu tiên lấy cái nào đây: Q. Tây hồ với mã C1_KIENND_MTS hay P.Bưởi Q.Tây Hồ với mã C1_BINHHT_MTS_XM
- Nếu lấy theo kết quả "dài" nhất thì lúc đó phải xem lại, có thể InStr sẽ không còn chính xác mà phải dùng LEN

Ưu tiên Phường trước Quận sau bác ạ

 

[ndu96081631]

Ưu tiên Phường trước Quận sau bác ạ

Nếu vậy thì sửa code lại 1 chút:

Function StLookup(LVal As String, LArray As Range, Optional ColIndex As Byte = 1) As String
  Dim Clls As Range, PosSt As Long, Temp As Long
  PosSt = Len(LVal) + 1
  For Each Clls In LArray.Resize(, 1)
    Temp = InStr(UCase$(LVal), UCase$(Clls))
    If Temp > 0 And Temp < PosSt Then
      PosSt = Temp
      StLookup = Clls(, ColIndex)
    End If
  Next Clls
End Function

Xem file và test hộ tôi nhé!

 

[braverynew]

Chuẩn rối bác ạ. Cám ơn bác nhiều