Forum Discussion
Deleting duplication,with exception
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?
- the only way to find out is to confirm from Ms. Rose G..