microsoft excel
I want to delete duplicates in excel sheet while comparing second column in sheet. I have an excel sheet with two columns A and B, while values repeat in both columns but i only want to remove values in column B which is appearing again against same value of column A. For example:
A B
ABC DEF
ABC GHI
ABC DEF
QWE DEF
QWE IKI
QWE DEF
—> want to remove DEF duplicate value against ABC and QWE, DEF should appear once against ABC and QWE.
Add a column to your table with the following formula filled down (adjust ranges to match your sheet):
=IF(B2="Cash",IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"<>Cash")>0,"Delete","Keep"),"Keep")
Then, apply a filter to the table and sort by the new column Z to A. Filter for rows with value "Delete".
Select the resulting rows, right-click them, and choose Delete Rows.
Then remove the filter and you should be left with what you want.
Your comment indicates that the duplicates are not necessarily sequential. Here is a formula that will handle that. Create a new display column in C and hide column B if desired. Say your data starts in row 2. Cell C2 would be:
=IF(ISERROR(MATCH(B2,C$2:C2,0)),B2,"")
Copy that down the column. This will look to see whether the value in column B already matches an entry in column C. If so, it returns a blank. Otherwise, it uses the column B value.
Best Answer
Select 2 Columns- go to Data- Remove Duplicates: