Removing Specific Duplicates from an Excel Array

microsoft excel

I have a workbook that has a list of account numbers and their attributes. Assume these are the only two columns on the sheet:

A      B
12345  Equity    
12345  Cash    
12345  Cash    
55555  Fixed Income    
55555  Cash

How might I remove the duplicate values that have Cash in column B, and keeping the other asset types (Equity for 12345 and Fixed Income for 55555)?

Best Answer

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".

filter steps

Select the resulting rows, right-click them, and choose Delete Rows.

Then remove the filter and you should be left with what you want.

enter image description here

Related Question