• 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.

Help to fix amend by If statement to show differences in multiple rows.

Hello
I have an IF statement that did show for multiple rows if there are the same data elements. - this worked great.
I need to amend the IF statement to so that it will only give me a "duplicate outcome" if the data from the different rows ALSO show DIFFERENT VALUES for two columns that I have highlighted in the attached excel.

Please can some once amend, and if there is an easier and faster!!!! way to process way, I would be very grateful - i have to run on 100,000 lines
this method is slow and kills the computer, so if there is someway better - that would be perfect
Basically,
column B, C, D,E,F, = should be the same
column G = must be different

If these conditions hold true - then we have a DUPLICATE.

Thank you!!!

my it statement was like
=IF(COUNTIFS($B$2:$B$24,$B2,$C$2:$C$24,$C2,$D$2:$D$24,$D2,$f$2:$f$24,$fH2)>1, "Duplicate row", "")

David
 

Attachments

  • dup row and different.xlsx
    12.4 KB · Views: 4
Hi

Thanks for reply and the response, but it does not help me.

I need ONE statement that takes ALL on the following conditions into account when deciding if there is a duplicate row. I must evaluate ALL criteria.
column B, C, D,E,F, = should be the same
column G = must be different

Please can someone help me with an updated response.

Excel file

Thanks

David.
 

Attachments

  • dup row and different.xlsx
    12.4 KB · Views: 7
A formula to do what you describe might be
Code:
= IF(
    COUNTIFS(
        [Ref], "<" & [@Ref],
        [Vendor], [@Vendor],
        [Reference], [@Reference],
        [Date], [@Date],
        [Amount], [@Amount],
        [Payee bank acct], [@[Payee bank acct]],
        [Clearing], "<>" & [@Clearing] ),
    "Duplicate",
    "" )

The only approach I can see to speed up the process is to first sort the table on the fields that should be tested for equality and then to compare only adjacent records to see whether there are instances that meet your criteria.
 

Attachments

  • dup row and different.xlsx
    14.6 KB · Views: 8
The attached version also contains a first cut at working with a sorted version of the data in which records are just compared with their immediate predecessors. I am not sure whether the yellow headed columns both need to be distinct or whether one will do.
Code:
= IF(
    ISREF( prior Invoice[#Headers] ),
    "",
    IF(
         AND(
               (current Data) = (prior Data),
               OR( (current [Clearing]) <> (prior [Clearing]), (current [Status]) <> (prior [Status]) ) ),
         "Duplicate",
         "" )
    )

Note; 'current' and 'prior' are relative references to entire records. The space is a range intersection returning a single value.
 

Attachments

  • dup row and different.xlsx
    21.1 KB · Views: 4
Test in I2:
=IF(SUMPRODUCT(($B$2:$B$10=B2)*($C$2:$C$10=C2)*($D$2:$D$10=D2)*($E$2:$E$10=E2)*($F$2:$F$10=F2)*($G$2:$G$10<>G2))>1,"Duplicate","")
copied down.
I don't think your I10 is correct.
 
Back
Top