• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight duplicate rows having same Positive & Negative Values

Status
Not open for further replies.

Sai Prabhu

New Member
Doc ID – There can be unique and duplicate IDs in DOC ID Column
Value – There can be Positive and Negative Value in the Value Column

Criteria : If there are duplicate Doc IDs and Value Column contains both Positive and Negative values (Numbers will be same), then can you please provide formula to highlight/indicate all the rows to be deleted.

Sample Data:

Doc ID Value Outcome
12345 -10 Delete Row
56789 5 Do Not Delete
52789 2 Do Not Delete
12478 -3 Do Not Delete
12345 10 Delete Row
12345 10 Delete Row
12345 5 Do Not Delete
 
Previous criteria perfectly meets the requirement. However,in addition to above criteria, I have a new requirement. Only rows having equal number of Negative and Positive values should be removed. In the below scenario, the DocID having 12345 and value 10 is repeated in 5 rows, out of which only 4 rows should be deleted since there are two negative values and only 2 positive values should be removed.

DocID Value Outcome HelperColumn Result
56789 5 Do Not Delete 283945 Don’t
52789 2 Do Not Delete 105578 Don’t
12478 -3 Do Not Delete -37434 Don’t
12345 5 Do Not Delete 61725 Don’t
12345 8 Do Not Delete 98760 Don’t
12345 -10 Delete Row -123450 Delete
12345 10 Delete Row 123450 Delete
12345 10 Delete Row 123450 Delete
12345 10 Do Not Delete 123450 Delete
12345 -10 Delete Row -123450 Delete

Please see the worksheet attached.

Thanks for you help.
 

Attachments

  • Dup Rows Example.xlsx
    9.1 KB · Views: 15
Hello

I need to remove the positive and negative values that match. But I need to meet the following criteria, the name should be the same in column D, in column I "Rate" they should be equal and also the negative amount must be zero between them in column F "Reg" for example if I have two Douglas with reg time +2 and -2 and rate R both of them I can delete those, but if I have the same criteria with rate O and R I can't delete.

Hope somebody can help me.

Thank you very much in advance.
 

Attachments

  • Week one data.xlsx
    38.8 KB · Views: 9
Hi PabloRod, welcome to the forum.
Allow me to say it is generally speaking a bad practice to hijack someone else's thread, certainly when it is an old one. It lowers your chances of getting a reply.
Now I reckon you've used the search to see if your question would not be answered already. That is positive and it is how it is supposed to be used.

However when it is not helping you, it is better to create a new thread. Also in this case you want to delete duplicates, not only highlight.
No formula can delete. It is either a manual job or to automate one needs a macro. We have a dedicated forum for that.

I've started something, but need to go to work now. By any change would you be fine with a power query solution? I just realized that might work. To be clear on the requirements:Tyler Mega 4 R is found on row 153 as duplicate of row 3, but it is repeated on row 165. It is supposed to be deleted only 1 once (1 pair)?
 
Hello Marc L

thank you very much! my apologies, actually this is my first time using a forum

Thank you very much for your help! is much appreciated!

I am ok to work with power query, and you are absolutely correct we just need to delete one pair, in this case Tyler Mega 4 R for sure we need to delete line 153 and also line 165, but also we need to delete another pair: line 148 and line 146 Tyler Mega 4 O

Thank you very much for your help!

best regards,
Pablo

in regards your questions we need to delete
Hi PabloRod, welcome to the forum.
Allow me to say it is generally speaking a bad practice to hijack someone else's thread, certainly when it is an old one. It lowers your chances of getting a reply.
Now I reckon you've used the search to see if your question would not be answered already. That is positive and it is how it is supposed to be used.

However when it is not helping you, it is better to create a new thread. Also in this case you want to delete duplicates, not only highlight.
No formula can delete. It is either a manual job or to automate one needs a macro. We have a dedicated forum for that.

I've started something, but need to go to work now. By any change would you be fine with a power query solution? I just realized that might work. To be clear on the requirements:Tyler Mega 4 R is found on row 153 as duplicate of row 3, but it is repeated on row 165. It is supposed to be deleted only 1 once (1 pair)?
 
Hi again @PabloRod,

Actually Marc liked my post, he did not offer any help. Just saying...

Have a look at my first attempt with PQ. I think it is working.
 

Attachments

  • Copy of Week one data.xlsx
    68.7 KB · Views: 11
Hello Again Grah - Guido

Thank you very much! unfortunately it don't fully work I still some lines that needs to be deleted, for example 36 and 31 Samuel Dabney 8.5 R, now that I am at work I dont have the PQ EPM in here, do you think is possible to work by formulas, I can take it from there and build a Macro.

Thanks again
 
PabloRod
As above has written as well as in Forum rules:
Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.

No more replies here!
 
Please checkout the solution of your query in which i have highlighted the duplicate cells. you can remove it or hold it as per your requirement.
 

Attachments

  • duplicate.xlsx
    9.4 KB · Views: 20
Status
Not open for further replies.
Back
Top