Comparing values

Question

Hi, I have data with countries in clusters, if country is matching it is duplicate, if not matching Not duplicate. If Country is blank for any of the records in the cluster, if should check for City. Same logic applies to city. Please find the table below

Record IDCluster IDCountryCityStateConclusionABCAUNITED STATESUNITED STATESUNITED STATESDuplicates, check for City and state if Country is blankDEFA UNITED STATESUNITED STATESDuplicates, check for City and state if Country is blankGHIAUNITED STATESUNITED STATESUNITED STATESDuplicates, check for City and state if Country is blankKLNBUNITED STATESUNITED STATESUNITED STATESNot Duplicates, Country is not matchingNMOBCANADA UNITED STATESNot Duplicates, Country is not matchingPQRCUNITED STATESUNITED STATESUNITED STATESDuplicates, check for City and state if Country is blankSTUCUNITED STATES UNITED STATESDuplicates, check for City and state if Country is blankWXYC UNITED STATESUNITED STATESDuplicates, check for City and state if Country is blankTHJCUNITED STATESUNITED STATESUNITED STATESDuplicates, check for City and state if Country is blankSCVDUNITED STATESUNITED STATESUNITED STATESNot Duplicates, Country is not matchingQSFDCANADAUNITED STATESUNITED STATESNot Duplicates, Country is not matchingOKRDCAYMAN ISLANDSUNITED STATESUNITED STATESNot Duplicates, Country is not matching

jrlindem · Answer

Like @OTrieger recommended, the Summarize Tool to perform some counts + Formula Tool to handle your hierarchy logic and comparison logic is the way I would go.  You can adapt and add additional logic from here, but this gives you a good starting point for how to accomplish this task:

Hope this helps you solve your need.  Cheers, -Jay

OTrieger · Answer

@Ayaanarjun-5885 
There will be several ways that you can deal with it. The fact that you have the clusters you can use a batch macro which will ease your work as it will handle cluster by cluster doing the same thing. So you will need to build one workflow and use it in a batch macro.
I would use Summarise Tool, grouping by country and counting, if you get higher count than 1 it will be duplicate, same goes with city. The other logic you will be able to use a formula tool using IF to check if country or city are populated or not.

There are other ways that this can be done, I just shared one way how to do it.