Phương pháp so sánh cột A và cột B trong Excel để loại bỏ và đánh dấu dữ liệu trùng

Compare two columns and find duplicates using Excel formulas

Excel được biết đến như một công cụ mạnh mẽ và xuất sắc cho việc tạo ra và xử lý các dãy dữ liệu cồng kềnh. Nếu bạn đang có một lượng lớn bảng tính và dữ liệu, hoặc thậm chí chỉ là một bảng tính to bự, và bạn cần so sánh hai cột với nhau để xác định các giá trị trùng nhau và thực hiện các hành động với phần trùng đó như loại bỏ hàng trùng, đánh dấu màu hoặc xóa nội dung trong các ô trùng nhau. Có thể hai cột này thuộc về một bảng, dù cạnh nhau hay không, hoặc chúng nằm trên hai trang tính riêng lẻ, hoặc thậm chí là hai bảng tính khác biệt.

Ví dụ, bạn có hai cột với các tên – 5 tên thuộc cột A và 3 tên trong cột B. Mục đích của bạn là so sánh các tên giữa hai cột để phát hiện những tên giống nhau. Đây chỉ ra một trường hợp đơn giản. Thực tế, khi so sánh trong các bảng tính, dữ liệu thường có thể lên tới hàng ngàn hoặc hàng vạn mục.

Trường hợp A: Khi cả hai cột đều nằm trong cùng một trang tính, chung một bảng dữ liệu: cột A và cột B.

Both columns are located on one sheet

Trường hợp B: Cột A và cột B nằm trên hai trang tính khác biệt: cột A tại Sheet2 và cột B tại Sheet3.

Two columns are located on different sheets

Phương pháp sử dụng công thức Excel để tìm kiếm giá trị giống nhau giữa hai cột:

Trường hợp 1: hai cột nằm trong cùng một danh sách

  1. Bạn nhập công thức sau vào ô trống đầu tiên, ở ô C1: =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),”Unique”,”Duplicate”)

Excel formula to compare data between 2 columns and find duplicate and unique entries

Công thức này sử dụng A1 làm ô dữ liệu đầu tiên từ cột muốn so sánh. $B$1$B$10000 là địa chỉ của ô đầu tiên và cuối cùng trong cột mà chúng ta so sánh với. Lưu ý sử dụng ký tự đô la ($) để định vị tham chiếu ô cố định – ký tự này đảm bảo địa chỉ ô không đổi khi công thức được sao chép xuống các ô khác.

Nhằm tìm dữ liệu trùng trong cột B, đổi cột trong công thức như sau: =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),”Unique”,”Duplicate”)

Bạn cũng có thể thay đổi nhãn từ “Unique”/”Duplicate” (Duy nhất/Trùng lặp) sang nhãn khác theo ý của bạn, như là “Not found”/”Found” (Không thấy/Đã thấy), hoặc chỉ sử dụng từ “Duplicate” và để trống (“”) thay vì “Unique”. Với cách này, ô sẽ trống nếu không tìm thấy giá trị trùng lập.

  1. Tiền hành sao chép công thức này cho toàn bộ cột C, cho đến dòng cuối cùng chứa dữ liệu của cột A. Để làm việc này, hãy đưa con trỏ về góc dưới bên phải của ô С1 – con trỏ sẽ chuyển thành hình dấu cộng màu đen như hình dưới:

Put the cursor to the lower right corner of the cell where you have written the formula

Kéo chuột trái và di chuyển xuống dưới để chọn tất cả các ô mà bạn muốn áp dụng công thức. Sau khi đã chọn xong tất cả ô, thả chuột.

Bạn có thể tăng tốc việc sao chép công thức trong bảng dữ liệu cỡ lớn bằng cách sử dụng tổ hợp phím nhanh. Bắt đầu bằng việc chọn ô chứa công thức cần sao chép là ô C1 bằng cách click vào nó, sau đó nhấn Ctrl + C để chép công thức vào bộ nhớ tạm. Tiếp theo, chọn mọi ô trong cột C chứa dữ liệu bằng tổ hợp phím Ctrl + Shift + End, và cuối cùng dùng Ctrl + V để dán công thức vào tất cả các ô đã chọn.

  1. Giá trị trùng trong các ô sẽ được đánh dấu là “Duplicate”.

Đối với tình huống B: khi hai cột nằm ở hai sheet khác nhau.

  1. Trong ô đầu tiên của cột trắng tại Sheet2 (cột B), điền công thức: =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),””,”Duplicate”)

Sheet3! đề cập đến trang chứa cột thứ hai, còn $A$1:$A$10000 là vùng dữ liệu trải từ ô đầu tiên đến cuối cùng của cột đó.

  1. Làm theo các bước tương tự như ở ví dụ A.
  2. Kết quả thu được sẽ là:

Đối phó với các giá trị bị trùng đã được tìm kiếm:

Giờ đây chúng ta đã xác định được các giá trị trong cột đầu tiên (A) cũng xuất hiện trong cột thứ hai (B). Đến lúc phải xử lý chúng.

Tìm kiếm và xem xét từng giá trị trùng lặp một cách thủ công trên toàn bảng sẽ mất nhiều thời gian và không hiệu quả. Có nhiều cách tiếp cận thay thế hiệu quả hơn.

Cách chỉ hiển thị những dòng có giá trị trùng lắp ở cột A:

Hãy thêm tiêu đề cho các cột nếu bạn chưa có sẵn. Để thêm, di chuyển con trỏ đến số thứ tự hàng đầu tiên (số 1) cho đến khi nó biến thành một mũi tên đen như hình dưới:

The cursor changes to a black arrow indicating that the entire row is selected.

Click chuột phải vào dòng vừa được chọn và chọn Insert từ menu:

Insert a new row

Đặt nhãn cho các cột như “Name”, “Duplicate?”, sau đó đi đến thẻ Data và chọn Filter:

Click vào mũi tên nhỏ xám ở cạnh “Duplicate?” để mở danh sách lựa chọn, bỏ tick chọn cho mọi lựa chọn ngoại trừ Duplicate, rồi nhấn OK:

Leave only 'Duplicate' checked in the drop-down list

Bằng cách này, cột A chỉ hiển thị các ô chứa dữ liệu trùng lặp với cột B. Trong ví dụ mẫu có 3 ô như thế.

Only duplicated entries are displayed

Muốn hiển thị lại các hàng ở cột A, hãy click vào icon filter tại cột B và chọn Select All. Hoặc có thể truy cập vào Data > Select & Filter > Clear như hình sau đây:

Cách làm nổi bật giá trị trùng thông qua việc tô màu:

Không chỉ dùng nhãn “Duplicate”, bạn cũng có thể đánh dấu những giá trị trùng lặp bằng cách tô màu chữ hoặc tô màu nền.

Sau khi bạn đã lọc những giá trị trùng như đã nêu trên, hãy chọn những ô đã được lọc rồi nhấn Ctrl + F1 để mở bảng Format Cells. Bạn có thể thử đổi màu nền những hàng trùng lặp thành màu vàng. Mặc dù có thể sử dụng chức năng Fill color tại thẻ Home để thay đổi màu nền, nhưng bảng Format Cells sẽ cho phép thực hiện toàn bộ việc định dạng cùng một lúc.

The Format Cells dialog box

Bạn giờ đây có thể dễ dàng xác định các ô có giá trị trùng lặp.

The background of duplicated cells is changed to yellow

Xóa bỏ những giá trị trùng lặp trong cột đầu tiên:

Hãy lọc ra những giá trị trùng nhau trong bảng, sau đó chọn chúng tất cả.

Trường hợp hai cột bạn đang so sánh ở hai sheet riêng biệt hoặc trong hai bảng khác nhau, hãy click chuột phải vào khu vực dữ liệu mà bạn đã chọn và click vào Delete Row từ menu.

Deleting duplicate rows

Khi Excel yêu cầu xác nhận hành động, hãy nhấn OK và sau đó bỏ bộ lọc. Kết quả là bạn chỉ nhìn thấy những hàng với giá trị không trùng lặp:

Only the rows with unique values are displayed

Tham khảo: Bộ sưu tập các phương pháp loại bỏ thông tin lặp lại

Nếu bạn có hai cột trong cùng một sheet, kế tiếp hoặc không kế tiếp nhau, việc loại bỏ những giá trị trùng nhau có thể trở nên phức tạp hơn. Chúng ta không thể xóa hết cả hàng chứa chúng vì như vậy sẽ ảnh hưởng đến dữ liệu cột 2 liền kề. Để chỉ hiển thị giá trị duy nhất trong cột A, hãy làm theo các bước sau đây:

  1. Lọc ra các giá trị trùng lặp, chọn toàn bộ và click chuột phải rồi chọn Clear Contents.

Excel - clear contents

  1. Loại bỏ bộ lọc đang áp dụng.
  2. Chọn toàn bộ các ô trong cột A, từ A1 cho tới ô cuối cùng chứa thông tin.
  3. Đi tới thẻ Data và click Sort A to Z. Trong cửa sổ dialog xuất hiện, chọn Continue with the current selection rồi ấn Sort.

Sort only column A

  1. Xóa đi cột chứa công thức, chừa lại chỉ mình “Uniques”.
  2. Bây giờ cột A chỉ còn lại dữ liệu không bị lặp lại so với cột B.

All duplicates are removed

Nội dung bạn đang xem là một phần của khóa học Excel từ cơ bản đến chuyên sâu của Siêu Marketing. Khóa học này giúp cung cấp tri thức toàn diện và có hệ thống về các hàm số, công cụ trong Excel và cách ứng dụng trong công việc. Hệ thống hiện đang có những ưu đãi hấp dẫn dành cho người đăng ký khóa học. Đừng chần chừ, tham gia ngay tại Siêu Marketing

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop