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.
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.
0 Comments