Flagging newly created duplicate rows

Ritika Bhalla

Hi,

I am using a sheet to maintain incoming requests for a system access. However, in case there are multiple identical requests, I only would like to trigger the associated workflow for the request that came in first.

I have used a formula to flag all the duplicate rows but is there a way to exclude the row that came in the first from this?

Genevieve P.

Hi Ritika,

Do you have a Created (Date) System Column in your sheet? If so, you could potentially use the MIN function with COLLECT to flag the earliest version of the same information. In fact, we could likely build this right into your current formula that's searching for duplicates... we could have it only flag the second, third, and fourth rows, but leave the first submission un-flagged.

Then one of your conditions in the workflow could be that there is no flag for that row.

This comment has an example of how to use MIN(COLLECT within an IF statement to check a box: https://community.smartsheet.com/discussion/comment/245210#Comment_245210

Let me know if this would work for you! If you need more help building it out, it would be very useful to see a screen capture of your sheet, identifying when the workflow would run (what triggers it), but please block out any sensitive data.

Cheers,

Genevieve

Ritika Bhalla

Hi @Genevieve P

Thank you for the help! I used the collect function with the max function instead to flag the rows that come in at a later timestamp. For your reference, in my formula, a request is considered duplicate if it has been made for the same "Requester" + "Module" + "Scope of Access" Combination.

I used the following in my sheet :

=IF([Access Requested On]@row = MAX(COLLECT([Access Requested On]:[Access Requested On], [Access Requested For]:[Access Requested For], [Access Requested For]@row, [Module Name]:[Module Name], [Module Name]@row, [Scope of Access]:[Scope of Access], [Scope of Access]@row)), 1, 0)

However, if a request comes in for the first time ever (meaning there are no duplicates), then this formula will flag that too, which is incorrect. It should only flag when there are more than 1 rows for the above combination.

Is there a way to achieve that?

Thanks

Ritika

Ritika Bhalla

Hi @Genevieve P

I think I figured this out. I am now maintaining a helper column with a count and in the above formula using a IF/AND combination to check this count to be greater than 1

Let me know if you think this sounds reasonable.

Thanks

Ritika

Paul Newcome

@Ritika Bhalla What about incorporating a COUNTIFS? Basically you would count how many rows match in the other columns but are less than the created column. This would solve your new entries problem.

=IF(COUNTIFS([Access Requested For]:[Access Requested For], [Access Requested For]@row, [Module Name]:[Module Name], [Module Name]@row, [Scope of Access]:[Scope of Access], [Scope of Access]@row, [Access Requested On]:[Access Requested On], @cell <= [Access Requested On]@row) > 1, 1)

M Sanchez

How can I do this but to keep the newly created column only?

Genevieve P.

Hi @M Sanchez

Can you elaborate on what you need to do? Screen captures would be helpful, but please block out sensitive data.

Cheers,

Genevieve