Data Validation – Remove Value Selected from Dropdown list

Việc tạo danh sách các giá trị để lựa chọn bằng Data Validation rất đơn giản. Tuy nhiên, nếu muốn xoá bỏ đi những giá trị đã được chọn rồi để nó không còn xuất hiện ở lần chọn tiếp theo thì khá phức tạp.

Chuẩn bị trước danh sách cần phân chia vào các nhóm như hình dưới. Mục đích là làm thế nào để nếu như đã chọn A lần thứ nhất thì A sẽ không xuất hiện lần thứ hai nữa.

Bước 1: Xác định các ô đặt dropdown list, ở đây là vùng G2:G10

Bước 2: Đếm trong vùng G2:G10 có bao nhiêu giá trị ở các ô từ A2 đến A10 xuất hiện ở vùng B2:B10. Nếu có thì để trống, nếu không có thì trả về vị trí của ô.
= IF(COUNTIF($E$2:$E$10,$A2)>=1,””,ROW(A1))
Mục đích của bước này là đánh số thứ tự tương ứng với danh sách, nếu giá trị nào xuất hiện rồi thì không đánh số nữa mà chỉ trả về ô trống.

Giá trị “C”, “F” và “G” đã được nhập ở Nhóm 1, nên cột B không trả về số thứ tự của “C”, “F” và “G” nữa.

Bước 3: Dời các số thứ tự đang có sao cho liền kề nhau, tức bỏ đi các ô trống ở cột B
=IF($B$2:$B$10<>0,SMALL($B$2:$B$10,ROW(A1)))
Kiểm tra trong vùng B2:B10 có giá trị nào khác rỗng không, nếu có thì lấy giá trị bé nhất đến lớn nhất. Ở đây có 3 giá trị bằng rỗng được trả về lỗi #NUM! ở cuối vùng.

Trường hợp này chúng ta có thể dùng IFERROR để tránh lỗi:

Bước 4: Tham chiếu đến các giá trị có cùng số thứ tự

Thử thay đổi các giá trị ở Nhóm 1:

Bước 5: Tạo danh sách động với danh sách chứa dữ liệu trong cột D, không lấy các ô trống trong vùng D2:D10
=OFFSET($D$2,0,0,COUNTA($D$2:$D$10)-COUNTBLANK($D$2:$D$10),1)
Mình gõ công thức ví dụ ở cột E nên phải dùng hàm mảng, tuy nhiên khi tạo dropdown list dùng OFFSET thì không cần mảng.

Cách tạo mảng động từ hàm OFFSET: https://phamnguyenkhanh.home.blog/2019/11/03/offset/