Flagging a row based on it being the original entry

06/30/21
Accepted

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.

Best Answer

  • Toufong VangToufong Vang ✭✭✭
    edited 07/01/21 Accepted Answer

    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!

Answers

  • Toufong VangToufong Vang ✭✭✭
    edited 07/01/21 Accepted Answer

    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!

  • Thank you! That was very helpful and worked perfectly!

Sign In or Register to comment.