Removing Duplicate Values in a Column Based on Values in Other Columns

[jlinkkpmg]

Hi all,

I'm trying to figure out how to remove a duplicate value in the Liability column based on the what is in the Award ID and Country columns.  See example below.

Award ID	Country	Liability	Apportioned Award
1	US	1,000	7,500
1	US	1,000	2,500
1	UK	5,000	1,000

I thought perhaps this could be done with a Multi-Row Formula, but I'm getting an error.

IF [Row-1:Award ID] = [Award ID]
AND [Row-1:Country] = [Country]
AND [Row-1:Liability] = [Liability]
THEN [Liability] = "0"

Once run, i would expect the results to look like:

Award ID	Country	Liability	Appoortioned Award
1	US	1,000	7,500
1	US	0	2,500
1	UK	5,000	1,000

Any suggestions?

Thank you.

[RodL]

Finish the expression with ENDIF.

And you may already have done this, but as a helpful hint, I would put a Sort tool right before the Multi-Row Formula tool to ensure that your groupings are consistent.

[patrick_digan · Accepted solution]

@jlinkkpmg I agree with @RodL. I've put his fix into a little workflow. By the way I personally prefer the IIF formula because I never have to type then/else/elseif/endif. In your case:

IIF([Row-1:Award ID] = [Award ID]
AND [Row-1:Country] = [Country]
AND [Row-1:Liability] = [Liability]
,[Liability] = "0"
,[Liability])

I also added another way to do it using a unique tool. I find this tool very powerful. Just my 2 cents

[jlinkkpmg]

Thanks to both for the quick replies.

[rahamrahimi]

[robertbrussell1]

This was very helpful!  I've been researching a similar issue and needed to remove duplicates if the Date Difference between duplicate record is >= 30 days.  

After sorting by Date (Ascending), I applied the Multi-Field Formula tool and included the following statement:

IF [Row-1:ID] = [ID] 
AND [Row-1:TYPE] = [TYPE]

THEN DateTimeDiff([DATE],[Row-1:DATE],"days")

Else Null()

Endif

This data can now be filtered accordingly.