First Entry Flag equation as column formula?

dorothy

Good morning! I'm trying to create a flag in my form receiver sheet to identify first entries vs. duplicates. This is to help me pull some metrics (counts) in other sheets. I have an equation that works row by row, but not in the very first row and also can't be converted to a column formula. What am I doing wrong? I'd like this check to run with every new form submission.

I didn't have a problem when I wasn't trying to separate first entry out from the duplicates

so I assume it has to do with limiting the count range?

SmartLew

Your range references specific cells (Leader$1) so it can't be a column formula.

Your forumla worked for just finding duplicates as the ranges are the whole column

So I understand, when there is a duplicate value, you are looking to identify the first submission and mark it as , first entry , and then any subsequent duplicates , mark as duplicate?

dorothy

Yes! Exactly! I need to keep the first entry in the pool for my metrics, but mark the subsequent ones so they are excluded?

SmartLew

Ok cool!

I would suggest creating a helper column which uses a =JOIN formula to join the three columns to create a single row value. At the moment you are counting the week, leader and commitment individually; these may all appear more than once in other row combinations that aren't values.

So

1.Helper column:

=JOIN(Week@row:Commitment@row)

2.Created date column

Add a created date column; this allows us to see date and time of entries

3.Duplicate Check Column

=IF(COUNTIFS([Join Column]:[Join Column], [Join Column]@row) > 1, IF(MIN(COLLECT([Created Date]:[Created Date], [Join Column]:[Join Column], [Join Column]@row)) = [Created Date]@row, "First Entry", "Duplicate"))

What this formula is saying is, if there is a duplicate value, return the oldest date/time that this duplicate value appeared. Then if that date/time matches the created date @ row, that is your oldest row and therefore the initial submission.

Hope that helps.

dorothy

NICE!! That makes a ton of sense; let me take a crack at it now. Thank you!

Jeffrey_PMO

Ok cool!<\/p>

I would suggest creating a helper column which uses a =JOIN formula to join the three columns to create a single row value. At the moment you are counting the week, leader and commitment individually; these may all appear more than once in other row combinations that aren't values.<\/p>

So <\/p>

1.Helper column: <\/strong><\/p>

=JOIN(Week@row:Commitment@row)<\/em><\/p>

2.Created date column<\/strong><\/p>

Add a created date column; this allows us to see date and time of entries<\/p>

3.Duplicate Check Column<\/strong><\/p>

=IF(COUNTIFS([Join Column]:[Join Column], [Join Column]@row) > 1, IF(MIN(COLLECT([Created Date]:[Created Date], [Join Column]:[Join Column], [Join Column]@row)) = [Created Date]@row, "First Entry", "Duplicate"))<\/em><\/p>

What this formula is saying is, if there is a duplicate value, return the oldest date\/time that this duplicate value appeared. Then if that date\/time matches the created date @ row, that is your oldest row and therefore the initial submission.<\/p>

Hope that helps.<\/p>","bodyRaw":"[{\"insert\":\"Ok cool!\\nI would suggest creating a helper column which uses a =JOIN formula to join the three columns to create a single row value. At the moment you are counting the week, leader and commitment individually; these may all appear more than once in other row combinations that aren't values.\\nSo \\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"1.Helper column: \"},{\"insert\":\"\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"=JOIN(Week@row:Commitment@row)\"},{\"insert\":\"\\n2.\"},{\"attributes\":{\"bold\":true},\"insert\":\"Created date column\"},{\"insert\":\"\\nAdd a created date column; this allows us to see date and time of entries\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"3.Duplicate Check Column\"},{\"insert\":\"\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"=IF(COUNTIFS([Join Column]:[Join Column], [Join Column]@row) > 1, IF(MIN(COLLECT([Created Date]:[Created Date], [Join Column]:[Join Column], [Join Column]@row)) = [Created Date]@row, \\\"First Entry\\\", \\\"Duplicate\\\"))\"},{\"insert\":\"\\nWhat this formula is saying is, if there is a duplicate value, return the oldest date\\\/time that this duplicate value appeared. Then if that date\\\/time matches the created date @ row, that is your oldest row and therefore the initial submission.\\nHope that helps.\\n\"}]","format":"rich","dateInserted":"2022-09-23T14:29:23+00:00","insertUser":{"userID":137711,"name":"SmartLew","title":"","url":"https:\/\/community.smartsheet.com\/profile\/SmartLew","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-29T13:14:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344553#Comment_344553","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/344553#Comment_344553

You have solved an issue I have been working on for over 4hours...thank you!