Deleting duplication,with exception

[Roseline]

Hi,

In the below table, I want to delete duplicate numbers from PKey column i.e., 333 is a duplicate; i want to delete item as HR and retain PR. The same should happen for other values as well
In the case of 555, which has occurred 2 times, Item is HR for both; I want to delete 1 duplicate HR row, an retain the other. Can anyone help me please.

Item PKey
HR 333
PR 333
HR 209
PR 209
HR 775
PR 775
HR 654
PR 654
HR 654
HR 555
HR 555
PR 667

[vichopalacios]

You can try this

Please Login or Register  to view this content.

[Roseline]

Thank you vichopalacios . I tried your code and it worked. But i noticed a scenario in my table which has 6 Items. if there is a PR & DR duplicate Pkey even the DR is getting deleted. which i dont want to be deleted. Can you please help me & sorry i missed mentioning this scenario in the original post.

Item PKey
HR 333
PR 333
HR 333
DR 333
PR 444
DR 444

[PCI]

if there is a PR & DR duplicate Pkey even the DR is getting deleted. which i dont want to be deleted.

Can you give more details to explain what is a duplicate ...and what is not a duplicate .
Is it only HR or PR as PKey value to consider ?

[PCI]

If only PKey like HR and PR are concerned try next code

Please Login or Register  to view this content.

[Roseline]

Hi,

here is the details information ,


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.

2.) 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

[PCI]

And in case of

HR 999
DR 999

PR 654
HR 654
HR 654

[PCI]

It seems you want to consider only some Item in the duplication
See next code and adjust
ItRef = "PR,HR"
With the list of items to treat

Please Login or Register  to view this content.

[Roseline]

Thanks for your response, PCI. I tried the 2nd code you sent; it is working; however, the result is showing all HR's, which is the exact opposite; the result should show only PR.
To your question about:
HR 999
DR 999,
If HR and DR are 999, then the result should display both HR and DR.

PR 654
HR 654
HR 654,
If PR, HR, HR are all 654, then the result should display only PR.

Some more scenarios:
PR 123
HR 123
DR 123
HR 123
If PR, HR, DR, HR are (for example 123), then the result should display only PR and DR.

HR 456
HR 456
If HR, HR, then only one HR should display.

[PCI]

the result should show only PR
So it means a duplicate is when PKey already exists and item = HR is it ?
What's up for

PR 234
PR 234

DR 567
DR 567

[Roseline]

Thanks for your questions, PCI. Below are my answers (within quotes) to your questions
the result should show only PR - "Yes, and DR too".
So it means a duplicate is when PKey already exists and item = HR is it ? - "Yes, duplicate is when PKey already exists, and it should be an HR, IF, there is a scenario where there is PR, DR and HR. If there is a scenario where there are 2 HR's, then 1 HR needs to be removed.
What's up for

PR 234
PR 234

DR 567
DR 567

"PR PR and DR DR scenarios do not exist".


Summarizing; there are always 3 Items PR, DR and HR - if this combination comes, then retain PR, DR and remove HR.
In some cases, my scenario won't even have a PR or DR; instead there might be 2 HR's, in which case, one of the 2 HR needs to be removed.
I hope this clarifies your doubts.

[PCI]

Is it what you need ??

Please Login or Register  to view this content.

[Roseline]

Yes, that worked. Thanks a lot for helping with this; I really appreciate it.

[PCI]

Ouf... we got it ..!
My pleasure.
HAve a lok for the rep and enjoy Excel

[Roseline]

Hi. After a few months, a scenario you had questioned about earlier (which I said wouldn't happen), has occurred.
In the below table, I want to delete duplicate values from both Item and PKey columns. For example: 00654 has 2 HRs, 2 PRs and 2 DTs. I want to delete duplicate values i.e., delete 1 PR, 1 HR and 1 DT, and retain the remaining 1 PR, 1 HR and 1 DT. The same should happen for other values as well
In the case of 555, which has occurred twice, Item is HR for both; I want to delete 1 duplicate HR row, an retain the other. The same 555 scenario applies to other item & Pkey values. Can you please help me.

Item PKey
HR 00654
PR 00654
HR 00654
PR 00654
DT 00654
DT 00654
HR 00775
PR 00775
DT 0054
PR 0054
HR 0054
HR 0054
HR 555
HR 555
PR 667
PR 667
PR 667
DT 444
DT 444
PR 444
HR 444
HR 444
PR 444

[PCI]

See next code
Take care that in column "B" there is some "blancks" in cells value: See 667 and 444

Please Login or Register  to view this content.

[Roseline]

Yes, that worked. Thanks a lot for helping with this; I really appreciate it.