Forum Discussion
Deleting duplication,with exception
Hi,
Can anyone help me to the below scenario.
In the below table, I want to delete duplicate numbers from PKey column
1.) if PKEY 333 is a duplicate; i want to delete ITEM as HR Row and retain PR & DR.
2.) if PKEY 209 is a duplicate; i want to delete ITEM as HR Row and retain PR .
4.)In the case of PKEY 555, which has occurred 2 times, ITEM is HR for both; I want to delete 1 duplicate HR row, an retain the other.
3.) If PKEY 222 is a duplicate , which shows the ITEM as PR & DR then the row should not delete.
ITEM PKEY
HR 333
PR 333
DR 333
HR 333
HR 209
PR 209
PR 775
HR 654
PR 654
HR 654
HR 555
HR 555
PR 667
DR 667
DR 808
HR 111
PR 222
DR 222
6 Replies
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"
