Bỏ qua nội dung chính

Làm thế nào để dễ dàng nối văn bản dựa trên các tiêu chí trong Excel?

Giả sử tôi có một cột số ID chứa một số trùng lặp và một cột tên, và bây giờ, tôi muốn ghép các tên dựa trên số ID duy nhất như hình minh họa bên trái, để nhanh chóng kết hợp văn bản dựa trên tiêu chí, làm thế nào chúng ta có thể làm trong Excel?

doc kết hợp văn bản dựa trên tiêu chí 1

Nối văn bản dựa trên tiêu chí với Chức năng do Người dùng Xác định

Nối văn bản dựa trên tiêu chí với Kutools cho Excel

Để kết hợp văn bản với các số ID duy nhất, trước tiên bạn có thể trích xuất các giá trị duy nhất và sau đó tạo Hàm do người dùng xác định để kết hợp các tên dựa trên ID duy nhất.

1. Lấy dữ liệu sau làm ví dụ, trước tiên bạn cần trích xuất các số ID duy nhất, vui lòng áp dụng công thức mảng sau: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),"")， Nhập công thức này vào ô trống, ví dụ D2, sau đó nhấn Ctrl + Shift + Enter các phím với nhau, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 2

Mẹo: Trong công thức trên, A2: A15 là phạm vi dữ liệu danh sách mà bạn muốn trích xuất các giá trị duy nhất, D1 là ô đầu tiên của cột mà bạn muốn đưa ra kết quả giải nén.

2. Và sau đó kéo chốt điều khiển điền xuống để trích xuất tất cả các giá trị duy nhất cho đến khi các khoảng trống được hiển thị, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 3

3. Trong bước này, bạn nên tạo Chức năng do người dùng xác định để kết hợp các tên dựa trên số ID duy nhất, vui lòng nhấn giữ ALT + F11 chìa khóa và nó mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

4. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Mô-đun Cửa sổ.

Mã VBA: nối văn bản dựa trên tiêu chí

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. Sau đó, lưu và đóng mã này, quay lại trang tính của bạn và nhập công thức này vào ô E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 4

6. Sau đó kéo chốt điền xuống các ô mà bạn muốn áp dụng công thức này và tất cả các tên tương ứng đã được kết hợp dựa trên số ID, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 5

Lời khuyên:

1. Trong công thức trên, A2: A15 là dữ liệu gốc mà bạn muốn kết hợp dựa trên, D2 là giá trị duy nhất bạn đã trích xuất và B2: B15 là cột tên mà bạn muốn kết hợp với nhau.

2. Như bạn có thể thấy, tôi đã kết hợp các giá trị được phân tách bằng dấu phẩy, bạn có thể sử dụng bất kỳ ký tự nào khác bằng cách thay đổi dấu phẩy “,” của công thức khi bạn cần.

Nếu bạn có Kutools cho Excel, Với khả Các hàng kết hợp nâng cao tiện ích, bạn có thể nhanh chóng và thuận tiện nối các cơ sở văn bản trên các tiêu chí.

Kutools cho Excel : với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày.
Đi tới Tải xuống
Dùng thử miễn phí 30 ngàyMua
PayPal / MyC Commerce

Sau khi cài đặt Kutools cho Excel, thực hiện như các bước sau:

1. Chọn phạm vi dữ liệu mà bạn muốn kết hợp dựa trên một cột.

2. Nhấp chuột Kutools > Hợp nhất & Tách > Các hàng kết hợp nâng cao, xem ảnh chụp màn hình:

3. Trong Kết hợp các hàng dựa trên cột hộp thoại, bấm vào cột ID và sau đó bấm Khóa chính để đặt cột này làm cột chính mà dữ liệu kết hợp của bạn dựa trên, hãy xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 7

4. Và sau đó nhấp vào Họ tên cột mà bạn muốn kết hợp các giá trị, sau đó nhấp vào Kết hợp và chọn một dấu phân tách cho dữ liệu kết hợp, xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 8

5. Sau khi hoàn tất các cài đặt này, hãy nhấp vào OK để thoát khỏi hộp thoại và dữ liệu trong cột B đã được kết hợp với nhau dựa trên cột chính A. Xem ảnh chụp màn hình:

doc kết hợp văn bản dựa trên tiêu chí 9

Với tính năng này, vấn đề sau sẽ được giải quyết trong thời gian sớm nhất:

Làm thế nào để kết hợp nhiều hàng thành một và tính tổng các bản sao trong Excel?

Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!

Kutools cho Excel: với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày. Tải xuống và dùng thử miễn phí ngay!

Công cụ năng suất văn phòng tốt nhất

🤖 Trợ lý AI của Kutools: Cách mạng hóa việc phân tích dữ liệu dựa trên: Thực thi thông minh   |  Tạo mã  |  Tạo công thức tùy chỉnh  |  Phân tích dữ liệu và tạo biểu đồ  |  Gọi các hàm Kutools...
Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao   |  Xóa hàng trống   |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu   |   Vòng không có công thức hữu ích. Cảm ơn !
Siêu tra cứu: Nhiều tiêu chí VLookup  |   VLookup Nhiều Giá Trị  |   VLookup trên nhiều trang tính   |   Tra cứu mờ ....
Danh sách thả xuống nâng cao: Tạo nhanh danh sách thả xuống   |  Danh sách thả xuống phụ thuộc   |  Danh sách thả xuống nhiều lựa chọn ....
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  |  So sánh dãy và cột hữu ích. Cảm ơn !
Các tính năng nổi bật: Tiêu điểm lưới   |  Chế độ xem thiết kế   |   Thanh công thức lớn   |  Trình quản lý sổ làm việc & trang tính   |  Thư viện tài nguyên (Văn bản tự động)   |  Bảng chọn ngày   |  Kết hợp các bảng tính   |  Mã hóa/Giải mã ô   |  Gửi email theo danh sách   |  Siêu lọc   |   Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu:  12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự,...)   |   50 + Biểu đồ Các loại (Biểu đồ Gantt,...)   |   40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh,...)   |   19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn,...)   |   12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ,...)   |   7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Chia ô,...)   |   ... và nhiều hơn nữa

Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian.  Bấm vào đây để có được tính năng bạn cần nhất...

Mô tả

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
Comments (38)
No ratings yet. Be the first to rate!
Laurie S.
Laurie S.
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
Reply
0
0
skyyang
skyyang     Laurie S.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
View Attachments (1 / 5)
Reply
Report
0
0
Md. Zaker Hossain
Md. Zaker Hossain
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
Reply
0
0
skyyang
skyyang     Md. Zaker Hossain
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
Reply
Report
0
0
Md. Zaker Hossain
Md. Zaker Hossain     skyyang
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
Reply
0
0
AA
AA
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
Reply
0
0
AS
AS
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
Reply
0
0
Pete
Pete
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
Reply
0
0
Merijn
Merijn
This comment was minimized by the moderator on the site
BTW i used the VBA solution
Reply
0
0
Merijn
Merijn
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
Reply
0
0
cadrose97
cadrose97
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
Reply
Report
0
0
skyyang
skyyang     cadrose97
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
Reply
Report
0
0
victor
victor
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
Reply
0
0
David
David
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
Reply
0
0
skyyang
skyyang     David
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For I = 1 To CriteriaRange.Count
If CriteriaRange.Cells(I).Value = Condition Then
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations