Flagging all duplicates

[kas]

Hello,

I need to include in my output a flag of all the duplicate records of one column, so the data in the other columns can be reviewed.

I know I can use the Multi row Formula tool with the formula below, but that will mark one of the duplicate records as 1 and the other as 0 - I need both to be marked as 1.

IF [Value = [Row-1:Value] THEN 1 ELSE 0

Example Current:

Value                 Name          DOB

123456789        Smith          1/1/1980

123456789        Smith          1/12/1980

568790561        Jones          3/8/1949 

Desired output:

Value                 Name        DOB              Flag

123456789        Smith        1/1/1980         1

123456789        Smith        1/12/1980       1

568790561        Jones        3/8/1949        0

How can I use this tool to achieve this?

Thank you,

Kim

[abacon · Accepted solution]

@kas Change you formula to this - IF [Value] = [Row-1:Value] THEN 1 ELSEIF [Value] = [Row+1:Value] THEN 1 ELSE 0. It is telling the records to look below AND above for a duplicate.

[kas]

@abacon 

It says "malformed statement" - this is what I put.

IF [SSN] = [Row-1:SSN] THEN 1 ELSEIF [SSN] = [Row+1:SSN] THEN 1 ELSE 0

I totally see your logic in the statement but can't see any error in my version.

Thanks again,

Kim

[abacon]

Throw an ENDIF at the end, I didn't complete the statement, that's my bad.

[davidskaife]

Hi @kas 

You need to finish the formula off with 'ENDIF'

[kas]

@abacon 

OH good grief...I know that!

Apparently my brain is completely shot today...

Worked like a charm :) 

Thanks so much!

[abacon]

All good, I feel that haha.