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

[david gabra]

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

 

[bosco_yip]

J2, copied down :

=IF(COUNTIF(G$2:G2,G2)=1,"Duplicate","")

Regards

 

[david gabra]

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.

 

[david gabra]

Please, any help would be much appreciated. It would nice to wake up with this solved

 

[Peter Bartholomew]

A formula to do what you describe might be

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

 

[Peter Bartholomew]

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.

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

 

[david gabra]

thanks - all good -really appreicate

 

[p45cal]

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.