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

https://community.smartsheet.com/discussion/comment/344553#Comment_344553

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