[Hepl] Xin giúp đỡ về tìm kiếm và thay thế trong VBA

[vucaodien09]

Chào anh chị,
Em có một file dữ liệu khá lớn cần thay thế địa chỉ. Cụ thể là nếu như A2 có chứa dữ liệu giống D2 : D32 thì B2 bằng chính là dữ liệu giống đó.
Rất mong anh chị giúp đỡ em với ạ!
Em xin chân thành cảm ơn ạ!

 

[batman1]

Em có một file dữ liệu khá lớn cần thay thế địa chỉ. Cụ thể là nếu như A2 có chứa dữ liệu giống D2 : D32 thì B2 bằng chính là dữ liệu giống đó.

Có vẻ như bạn muốn tìm Xã, nhưng tìm Xã và đỏ đỏ không hẳn là tương đương. Tức cái ở cột A mà được tìm thấy trong cột D có thể không là Xã mà có thể là tên Xóm, tên Huyện nào đó. Nhưng thôi, hỏi đỏ đỏ thì công thức cho đỏ đỏ ở B2

=LOOKUP(2;1/COUNTIF($A2;"*"&$D$2:$D$32&"*");$D$2:$D$32)

Dữ liệu cột D hiện tới dòng 32, sửa lại trong công thức cho đúng nếu cần.

 

[vucaodien09]

Có vẻ như bạn muốn tìm Xã, nhưng tìm Xã và đỏ đỏ không hẳn là tương đương. Tức cái ở cột A mà được tìm thấy trong cột D có thể không là Xã mà có thể là tên Xóm, tên Huyện nào đó. Nhưng thôi, hỏi đỏ đỏ thì công thức cho đỏ đỏ ở B2

=LOOKUP(2;1/COUNTIF($A2;"*"&$D$2:$D$32&"*");$D$2:$D$32)

Dữ liệu cột D hiện tới dòng 32, sửa lại trong công thức cho đúng nếu cần.

Em cảm ơn anh chị ạ. Hàm bên trên đã cho kết quả đúng những gì em muốn ạ. Nếu làm nó trong VBA thì em cần viết như thế nào ạ?

 

[batman1]

Em cảm ơn anh chị ạ. Hàm bên trên đã cho kết quả đúng những gì em muốn ạ. Nếu làm nó trong VBA thì em cần viết như thế nào ạ?

Công thức chưa chuẩn. Bạn thấy là kết quả B21 = Hải Phú, B23 = B24 = Hải An. Kết quả đúng phải là B21 = Hải Phúc, B23 = B24 = Hải Anh

Kết quả sai do có cả Hải An và Hải Anh, Hải Phú và Hải Phúc đều có trong cột D, mà với thứ tự dữ liệu ở cột D như hiện tại thì tìm thấy Hải Phú và Hải An thay cho Hải Phúc và Hải Anh.

Tôi đề nghị sắp xếp cột D tăng dần theo số ký tự, và với các dữ liệu cùng có số ký tự thì sắp xếp tăng dần theo dữ liệu. Tức nhập công thức vào C2

=LEN(D2)

Sắp sếp theo 2 cột C, D, trước hết theo C sau đó theo D, cả hai đều sắp xếp tăng dần.

Cũng với sắp xếp như trên code

Sub thaythe()
Dim lastRow As Long, count As Long, r As Long, k As Long, diachi(), xa(), kq()
    With ThisWorkbook.Worksheets("Sheet1")
        lastRow = .Cells(Rows.count, "A").End(xlUp).Row ' dong cuoi o cot A
        If lastRow = 1 Then Exit Sub    ' khong co du lieu thi nghi choi
        diachi = .Range("A2:A" & lastRow + 1).Value ' lay dia chi vao mang diachi, lay du 1 dong cuoi
        lastRow = .Cells(Rows.count, "D").End(xlUp).Row ' dong cuoi o cot D
        If lastRow = 1 Then Exit Sub    ' khong co du lieu thi nghi choi
        xa = .Range("D2:D" & lastRow + 1).Value ' lay XA vao mang xa, lay du 1 dong cuoi
    End With
    ReDim kq(1 To UBound(diachi, 1) - 1, 1 To 1)
'    count = lastRow - 1 ' so dong trong mang xa
    For r = 1 To UBound(diachi, 1) - 1 ' duyet mang dia chi diachi, khong duyet dong lay them
        For k = UBound(xa, 1) - 1 To 1 Step -1 ' duyet mang xa
            If InStr(1, diachi(r, 1), xa(k, 1), vbTextCompare) Then ' tim thay trong mang xa
                kq(r, 1) = xa(k, 1)     ' lay ten Xa vao mang ket qua kq
                Exit For    ' ra khoi vong FOR do da tim thay
            End If
        Next k
    Next r
    ThisWorkbook.Worksheets("Sheet1").Range("B2").Resize(UBound(kq, 1)).Value = kq  ' lay ket qua vao cot B
End Sub

Lưu ý: Tôi chỉ làm xong việc cho bạn thôi. Rất có thể công thức và code chưa chính xác. Và đặc biệt tôi chỉ làm ra kết quả. Tôi không tham gia thi code ngắn nhất, tối ưu nhất.

 

[vucaodien09]

Công thức chưa chuẩn. Bạn thấy là kết quả B21 = Hải Phú, B23 = B24 = Hải An. Kết quả đúng phải là B21 = Hải Phúc, B23 = B24 = Hải Anh

Kết quả sai do có cả Hải An và Hải Anh, Hải Phú và Hải Phúc đều có trong cột D, mà với thứ tự dữ liệu ở cột D như hiện tại thì tìm thấy Hải Phú và Hải An thay cho Hải Phúc và Hải Anh.

Tôi đề nghị sắp xếp cột D tăng dần theo số ký tự, và với các dữ liệu cùng có số ký tự thì sắp xếp tăng dần theo dữ liệu. Tức nhập công thức vào C2

=LEN(D2)

Sắp sếp theo 2 cột C, D, trước hết theo C sau đó theo D, cả hai đều sắp xếp tăng dần.

Cũng với sắp xếp như trên code

Sub thaythe()
Dim lastRow As Long, count As Long, r As Long, k As Long, diachi(), xa(), kq()
    With ThisWorkbook.Worksheets("Sheet1")
        lastRow = .Cells(Rows.count, "A").End(xlUp).Row ' dong cuoi o cot A
        If lastRow = 1 Then Exit Sub    ' khong co du lieu thi nghi choi
        diachi = .Range("A2:A" & lastRow + 1).Value ' lay dia chi vao mang diachi, lay du 1 dong cuoi
        lastRow = .Cells(Rows.count, "D").End(xlUp).Row ' dong cuoi o cot D
        If lastRow = 1 Then Exit Sub    ' khong co du lieu thi nghi choi
        xa = .Range("D2:D" & lastRow + 1).Value ' lay XA vao mang xa, lay du 1 dong cuoi
    End With
    ReDim kq(1 To UBound(diachi, 1) - 1, 1 To 1)
'    count = lastRow - 1 ' so dong trong mang xa
    For r = 1 To UBound(diachi, 1) - 1 ' duyet mang dia chi diachi, khong duyet dong lay them
        For k = UBound(xa, 1) - 1 To 1 Step -1 ' duyet mang xa
            If InStr(1, diachi(r, 1), xa(k, 1), vbTextCompare) Then ' tim thay trong mang xa
                kq(r, 1) = xa(k, 1)     ' lay ten Xa vao mang ket qua kq
                Exit For    ' ra khoi vong FOR do da tim thay
            End If
        Next k
    Next r
    ThisWorkbook.Worksheets("Sheet1").Range("B2").Resize(UBound(kq, 1)).Value = kq  ' lay ket qua vao cot B
End Sub

Lưu ý: Tôi chỉ làm xong việc cho bạn thôi. Rất có thể công thức và code chưa chính xác. Và đặc biệt tôi chỉ làm ra kết quả. Tôi không tham gia thi code ngắn nhất, tối ưu nhất.

Bài toán thực tế của em nó loằng ngoằng hơn một tí. Nhưng lời giải của anh chị đã thực sự khai sáng cho em. Dựa trên bộ khung này, em sẽ cố gắng áp dụng vào trường hợp của mình. Một lần nữa, xin được gửi tới anh chị lời cảm ơn chân thành nhất!

 

[SA_DQ]

Chủ bài đăng chú í: Đáp án còn 1 chỗ sai
:

. . . . . . . Địa chỉ

. . . . . . . Thay thế

. . . . . . . Xã

Xóm 2A, Xã Hải Anh, Huyện Hải Hậu, Tỉnh Nam Định

Hải An

Hải Quang

Xóm 2A, Xã Hải Anh, Huyện Hải Hậu, Tỉnh Nam Định

Hải An

Hải Thanh

Xóm Tây Sơn, Xã Hải Chính, Huyện Hải Hậu, Tỉnh Nam Định

Hải Chính

Hải Đông

Hải Cường - Hải Hậu - Nam Định

Hải Cường

Hải Anh

Xóm Xuân Hà,Hải Đông, Hải Hậu, Nam Định

Hải Đông

Hải Châu

Xóm 16, Hải Hưng, Hải Hậu, Nam Định

Hải Hưng

Hải Triều

Xóm 6, Hải Long, Hải Hậu, Nam Định

Hải Long

Hải Vân

Xóm 2, Xã Hải Long, Huyện Hải Hậu, Tỉnh Nam Định

Hải Long

Hải Hòa

Xóm 6, Xã Hải Minh, Huyện Hải Hậu, Tỉnh Nam Định

Hải Minh

Hải Tây

Xóm 19, Xã Hải Nam, Huyện Hải Hậu, Tỉnh Nam Định

Hải Nam

Hải Minh

Xã Hải Phúc, Huyện Hải Hậu, Tỉnh Nam Định

Hải Phú

Hải Hà

Xã Hải Phương-Huyện Hải Hậu

Hải Phương

Hải Phương

Xóm 11, Xã Hải Phương, Huyện Hải Hậu, Tỉnh Nam Định

Hải Phương

Hải Phong

Xóm xướng cau, Xã Hải Thanh, Huyện Hải Hậu, Tỉnh Nam Định

Hải Thanh

Hải Bắc

Xóm 8, Xã Trực Thái, Huyện Trực Ninh, Tỉnh Nam Định

Hải Sơn

cường liêm, Xã Trực Đại, Huyện Trực Ninh, Tỉnh Nam Định

Hải Nam

Xóm Trung Thành 1, Xã Việt Hùng, Huyện Trực Ninh, Tỉnh Nam Định

Hải Phúc

Việt Hùng - Trực Ninh - Nam Định

Hải Lý

Liên Phú, Thị trấn Cát Thành, Huyện Trực Ninh, Tỉnh Nam Định

Hải Chính

Nam Cường, Xã Việt Hùng, Huyện Trực Ninh, Tỉnh Nam Định

Hải Phú

Xã Trực Thái - Huyện Trực Ninh - Tỉnh Nam Định

Hải Đường

Xóm Nguyễn Chẩm A, Thị trấn Cồn, Huyện Hải Hậu, Tỉnh Nam Định

Hải Ninh

Lâm Dũng, Thị trấn Quất Lâm, Huyện Giao Thủy, Tỉnh Nam Định

Hải Long

Giao Yến, Giao Thủy, Nam Định

Hải Tân

Trực Thắng - Trực Ninh - Nam Định

Hải Hưng

Ninh Tiến, Thị trấn Quất Lâm, Huyện Giao Thủy, Tỉnh Nam Định

Hải Lộc

Thị trấn Yên Định-Huyện Hải Hậu

Hải Trung

Tổ 1, Xã Trực Chính, Huyện Trực Ninh, Tỉnh Nam Định

Hải Xuân

Hải Cường

Hải An

Hải Giang

 

[vucaodien09]

Chủ bài đăng chú í: Đáp án còn 1 chỗ sai
:

. . . . . . . Địa chỉ

. . . . . . . Thay thế

. . . . . . . Xã

Xóm 2A, Xã Hải Anh, Huyện Hải Hậu, Tỉnh Nam Định

Hải An

Hải Quang

Xóm 2A, Xã Hải Anh, Huyện Hải Hậu, Tỉnh Nam Định

Hải An

Hải Thanh

Xóm Tây Sơn, Xã Hải Chính, Huyện Hải Hậu, Tỉnh Nam Định

Hải Chính

Hải Đông

Hải Cường - Hải Hậu - Nam Định

Hải Cường

Hải Anh

Xóm Xuân Hà,Hải Đông, Hải Hậu, Nam Định

Hải Đông

Hải Châu

Xóm 16, Hải Hưng, Hải Hậu, Nam Định

Hải Hưng

Hải Triều

Xóm 6, Hải Long, Hải Hậu, Nam Định

Hải Long

Hải Vân

Xóm 2, Xã Hải Long, Huyện Hải Hậu, Tỉnh Nam Định

Hải Long

Hải Hòa

Xóm 6, Xã Hải Minh, Huyện Hải Hậu, Tỉnh Nam Định

Hải Minh

Hải Tây

Xóm 19, Xã Hải Nam, Huyện Hải Hậu, Tỉnh Nam Định

Hải Nam

Hải Minh

Xã Hải Phúc, Huyện Hải Hậu, Tỉnh Nam Định

Hải Phú

Hải Hà

Xã Hải Phương-Huyện Hải Hậu

Hải Phương

Hải Phương

Xóm 11, Xã Hải Phương, Huyện Hải Hậu, Tỉnh Nam Định

Hải Phương

Hải Phong

Xóm xướng cau, Xã Hải Thanh, Huyện Hải Hậu, Tỉnh Nam Định

Hải Thanh

Hải Bắc

Xóm 8, Xã Trực Thái, Huyện Trực Ninh, Tỉnh Nam Định

Hải Sơn

cường liêm, Xã Trực Đại, Huyện Trực Ninh, Tỉnh Nam Định

Hải Nam

Xóm Trung Thành 1, Xã Việt Hùng, Huyện Trực Ninh, Tỉnh Nam Định

Hải Phúc

Việt Hùng - Trực Ninh - Nam Định

Hải Lý

Liên Phú, Thị trấn Cát Thành, Huyện Trực Ninh, Tỉnh Nam Định

Hải Chính

Nam Cường, Xã Việt Hùng, Huyện Trực Ninh, Tỉnh Nam Định

Hải Phú

Xã Trực Thái - Huyện Trực Ninh - Tỉnh Nam Định

Hải Đường

Xóm Nguyễn Chẩm A, Thị trấn Cồn, Huyện Hải Hậu, Tỉnh Nam Định

Hải Ninh

Lâm Dũng, Thị trấn Quất Lâm, Huyện Giao Thủy, Tỉnh Nam Định

Hải Long

Giao Yến, Giao Thủy, Nam Định

Hải Tân

Trực Thắng - Trực Ninh - Nam Định

Hải Hưng

Ninh Tiến, Thị trấn Quất Lâm, Huyện Giao Thủy, Tỉnh Nam Định

Hải Lộc

Thị trấn Yên Định-Huyện Hải Hậu

Hải Trung

Tổ 1, Xã Trực Chính, Huyện Trực Ninh, Tỉnh Nam Định

Hải Xuân

Hải Cường

Hải An

Hải Giang

Dạ em cảm ơn anh chị ạ. Lỗi đấy em sửa bằng cách sắp xếp cột xã từ A tới Z ạ.

 

[batman1]

Chủ bài đăng chú í: Đáp án còn 1 chỗ sai

Lỗi này tôi đã tự phát hiện ở bài #4 rồi.

 

[batman1]

Dạ em cảm ơn anh chị ạ. Lỗi đấy em sửa bằng cách sắp xếp cột xã từ A tới Z ạ.

Tôi ghi là phải sắp xếp trước tiên theo số ký tự bởi ...

Hiện thời tôi chưa nghĩ ra dữ liệu ví dụ sát với thực tế, nhưng với dữ liệu lý thuyết thì nếu chỉ sắp xếp theo cột D mà không sắp xếp theo cột C thì có thể kết quả sai.

Bạn kiểm tra như sau:
1. Sửa

Xóm 2A, Xã Hải An, Huyện Hải Hậu, Tỉnh Nam Định

Xóm 2A, Xã Hải Anh, Huyện Hải Hậu, Tỉnh Nam Định thành Xóm 2A, Xã ABCD, Huyện Hải Hậu, Tỉnh Nam Định Xóm 2A, Xã BCD, Huyện Hải Hậu, Tỉnh Nam Định 2. Sửa trong cột D Hải An Hải Anh thành BCD ABCD 3. chỉ sắp xếp cột D tăng dần. Kết quả bạn sẽ có ... BCD ... ... BCD ...