Remove elements in one list from the other in excel

There are times when we need to compare two lists in excel and remove the elements from original list.
1. Suppose List A the original BIG list and List B the list containing values that you want to remove from List A.

2. Sort and Remove duplicates from your original LONG list in List A by going to Data>Remove duplicates
3. Now add the List B under the List A in the same column as List A
4. And again sort the consolidated list.
5. Paste following formula in cell B2. A1 cell should be empty, and if it is not then insert a row on top, so that cell A1 is empty.
                      =IF(OR(TRIM(A1)=TRIM(A2),TRIM(A2)=TRIM(A3)),"",TRIM(A2))
6. Copy this formula to all cells in column B, and ure done. You have a list free of all elements in List B

7. Now copy column B and paste it somewhere else as paste special>values. Just sort and the list is final.

Post a comment

0 Comments