Flagging a row based on it being the original entry

Kenneth Wittich · 06/30/21

I have a sheet that is populated through a form. My employees look at many files a day and some are duplicates from the same day, previous days, weeks, or months. I want only the original file to count in my tally, so I am trying to build a formula that will automatically mark or flag the subsequent file(s) as duplicate based on the entry date.

My submission form captures the File#, Date/Time stamp, and additional information.

Toufong Vang · 07/01/21

Hi, @Kenneth Wittich

Try...

=IF([Date/Time stamp]@row = MIN(COLLECT([Date/Time stamp]:[Date/Time stamp], [File#]:[File#], [File#]@row)),"This is the oldest entry.", "Nope")

The formula assumes that the timestamp for each row will be different.

The approach...

COLLECT() all of the timestamps from the timestamp column where the File# are the same as the File# at the current row. Then find the minimum (oldest) timestamp. If the timestamp for the current row is the same as the minimum timestamp, then the current row must be the oldest for that set of File#.

COLLECT([Date/Time stamp]:[Date/Time stamp], [File#]:[File#], [File#]@row)

Now get the oldest timestamp...

MIN(COLLECT([Date/Time stamp]:[Date/Time stamp], [File#]:[File#], [File#]@row))

Apply the logic...

IF([Date/Time stamp]@row = MIN(COLLECT([Date/Time stamp]:[Date/Time stamp], [File#]:[File#], [File#]@row)),"This is the oldest entry.", "Nope")

Hope this helps!

Toufong Vang · 07/01/21