How do I setup a flag for duplicates?

Silvia Rangel · 12/17/19

Hello,

First time posting here ☺

Trying to setup a flag for when the same data is entered in a row, but it's giving me a hard time ☺

help?

Andrée Starå · 12/17/19

@srangel Happy to help!

Sorry about that! Remove the ” ” surrounding the number 1 and it should work.

cpfeifer · 05/31/22

L_123

May 27, 2022

you can try to wrap it in an iferror(formula,0)

This worked, thanks!

L_123 · 12/17/19

you mean something like

=if([Assigned To (Change)]@row = [Requested For]@row,1,0)

or do you have more than 2 columns you want to search for?

Silvia Rangel · 12/17/19

I mean if the same value for Requested For is entered more than once in the same row:

=IF([Requested For]@row=[Requested For]@row, 1, 0) is giving me a red flag for all rows, even the ones not duplicated.

thanks!

Andrée Starå · 12/17/19

Hi,

Welcome to the Community and the wonderful world of Smartsheet!

Try something like this.

=IF(COUNTIF([Requested For]:[Requested For]; [Requested For]@row) > 1; "1")

The same version but with the below changes for your and others convenience.

=IF(COUNTIF([Requested For]:[Requested For], [Requested For]@row) > 1, "1")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Silvia Rangel · 12/17/19

Thanks! Wonderful indeed ;-)

I am not sure, I'm getting a number instead of a red flag....

Andrée Starå · 12/17/19

@srangel Happy to help!

Sorry about that! Remove the ” ” surrounding the number 1 and it should work.

Silvia Rangel · 12/17/19

My bad!

=IF(COUNTIF([Requested For]:[Requested For], [Requested For]1) > 1, "Red")

It works, only thing is it does not work for a flag, only with a red light symbol... Any ideas why??

Andrée Starå · 12/17/19

@srangel Did you get it working with the flag as well?

cpfeifer · 05/27/22

@Andrée Starå This formula worked for me (thank you!) and the flags are showing for duplicate rows but it's firing "invalid" for some rows and I'm not sure why. I'm trying to find duplicates that have a match to two columns, 1) Name 2) identification number, since the same person could have multiple non-duplicative entries. Can you help solve why some of these are showing as "invalid" but many are working?

My formula currently is:

=IF(COUNTIF([Name (person who submits info)]:[Name (person who submits info)], [Name (person who submits info)]@row) > 1, AND(IF(COUNTIF([Study Number]:[Study Number], [Study Number]@row) > 1, 1)))

Thanks for your help!

Clare

L_123 · 05/27/22

you can try to wrap it in an iferror(formula,0)

Andrée Starå · 05/27/22

Hi @cpfeifer

I hope you're well and safe!

Excellent!

Glad to hear that it was helpful!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!