looking for duplicates across multiple rows and values in multiple columns

Question

I am in need of finding total duplicates in a CSV file where there is multiple criteria for what is considered a duplicate.

This is what I need to check against using a CSV that has millions of records.

IF
(!IsEmpty([FIRSTNAME]) AND [FIRSTNAME]=[FIRSTNAME] AND
    !IsEmpty([LASTNAME]) AND [LASTNAME]=[LASTNAME] AND
    !IsEmpty([BILLINGADDRESS1]) AND [BILLINGADDRESS1]=[BILLINGADDRESS1] AND
    !IsEmpty([BILLINGZIPCODE]) AND [BILLINGZIPCODE]=[BILLINGZIPCODE]
) 
OR
(!IsEmpty([FIRSTNAME]) AND [FIRSTNAME]=[FIRSTNAME] AND
    !IsEmpty([LASTNAME]) AND [LASTNAME]=[LASTNAME] AND
    !IsEmpty([BILLINGCITY]) AND [BILLINGCITY]=[BILLINGCITY] AND
    !IsEmpty([BILLINGZIPCODE]) AND [BILLINGZIPCODE]=[BILLINGZIPCODE]
) 
OR
(!IsEmpty([FIRSTNAME]) AND [FIRSTNAME]=[FIRSTNAME] AND
    !IsEmpty([LASTNAME]) AND [LASTNAME]=[LASTNAME] AND
    !IsEmpty([BILLINGZIPCODE]) AND [BILLINGZIPCODE]=[BILLINGZIPCODE]
)
OR
(!IsEmpty([FIRSTNAME]) AND [FIRSTNAME]=[FIRSTNAME] AND
    !IsEmpty([LASTNAME]) AND [LASTNAME]=[LASTNAME] AND
    !IsEmpty([PHONE]) AND [PHONE]=[PHONE]
)
OR
(!IsEmpty([FIRSTNAME]) AND [FIRSTNAME]=[FIRSTNAME] AND
    !IsEmpty([LASTNAME]) AND [LASTNAME]=[LASTNAME] AND
    !IsEmpty([EMAIL]) AND [EMAIL]=[EMAIL]
)
THEN 1  ELSE -1 ENDIF

Based on the above criteria the below would be True or False, if I did not miss anything in my checking.

Screen Shot 2022-03-15 at 5.44.51 PM.png

Watermark · Answer

From there you can screen a piece at a time. Sometimes it's better to have a few tools and be clear at each step, instead of trying to impose a complex set of rules all wrapped together into 1.

Watermark · Answer

Even without a data set, looking at what you have above, I'd be more inclined to leverage at least a filter to start off with.  Everyone of the above have the commonality of:

!IsEmpty([FIRSTNAME]) AND [FIRSTNAME]=[FIRSTNAME]

AND

!IsEmpty([LASTNAME]) AND [LASTNAME]=[LASTNAME] AND

Filter that T/F and if it doesn't meet the criteria then apply -1.

From there your criteria simplifies greatly for your 4 others. Just drop the first part out if it doesn't meet the name conditions.

stock · Answer

Here is the same sample data that I included in the screenshot.

FIRSTNAMELASTNAMEBILLINGADDRESS1BILLINGZIPCODEBILLINGCITYPHONEEMAILJohnDean123 main Street29210anywhere5554444444john@email.comJohnDean123 main Street29210anywhere5554444444john@email.comJamesSmith444 west ave12345somewhere2223334444james@email.comBobBird555 w 1000 s55548 6665552222bob@email.comBobBird555 w 1000 s hometown6665552222bob@email.comMHanks222 18th ave66589nowhere6665552222mrhanks@email.comMaryHanks222 18th ave66589nowhere6665552222maryh@email.comBaryJones510 laurel street55878outback5558887777bary@email.comBaryJones 55878outback5558887777bary@email.comDarrelWilliams555 another street87898lower fourty5558887777bary@email.comDarlinePuts   3335468888darline@email.comDarlinePuts   3335468888darline@email.comHenryBarks245 west winding way54898none5789789988henry@email.comRalphieFoo   6589658965 RalphieFoo   6589658965