Forum Discussion
Deleting duplication,with exception
Hia,
I've attached one way of doing it, its 2 steps, but they are easy. First add this formula:
=IF(AND(COUNTIF($B$1:B1,B2),A2="HR"),1,0)
You will need to tweak it a bit depending on where your data is.
Then filter the column with this formula for 1 and delete all the rows.
I have attached a workbook so you can see it working.
You absolutely could write something in VBA that would delete the rows for you without any intervention but this seems less likely to go wrong.
Mr. West
I was fascinated by the query of Ms. Rose G ..
I want to learn some new techniques.
just curious, shouldn't the formula be referencing the whole column B?
also her criteria specially 4) must retain one duplicate..
I think only a VBA can handle these criteria (which is way out of my league!)
good luck Ms. Rose G...
thank you Mr. West..
From the list of conditions in the original post I thought it broke down to just this.. Is the number a duplicate and the other item "HR"
=IF(AND(COUNTIF($B$1:B1,B2),A2="HR"),1,0)
so what I did here is an IF that registers 1 or 0. The COUNTIF($B$1:B1,B2) part will check for the number in B2 in the column above B2, It would probably make more sense to look at it from further down the column so checking B12 it would now look like COUNTIF($B$1:B11,B12). The idea is that if the number appears in the column above this must be a duplicate. Then AND checks that A2 is also "HR"
I tried the formula below:
=IF(AND(COUNTIF(B:B,B2),A2="HR"),1,0)
and it did too... problem is the criteria 4) where it will only get rid of one duplicate.in the 1st and 2nd criteria - it want to delete HR but if it has a duplicate - it wants to retain one....
wonder how to insert that criteria into the formula....
BTW - the filtering is neat!
thanks..You are right :(
This might be better:
=IF(AND(A2="HR",ISNUMBER(MATCH(A2&B2,A$1:$A1&B$1:$B1,0))),1,"")
entered as an array, but now I'm wondering if in the first condition both HR333s are meant to be deleted?