Remove Old Form Entries

Options

I have a sheet where users scan a barcode into a form. I would like the sheet to remove the old entry if the barcode is scanned again.

So, user scans in 1234 with a note "Checked out"

The then scans the same 1234 barcode in with a note "Checked in"

I want to to drop the first "Checked out" row or move it to another sheet.

Any help would be appreciated.

Thank you,

Answers

  • Paulo Ferrer
    Paulo Ferrer ✭✭✭✭
    Options

    You can use automation for this.

    Create a column with a flag ("checked out") that identifies the oldest row and mark it.

    When changing the value of this "flag" column, use automation to move the row to another sheet. This automation template is standard.

  • WalterHartman
    WalterHartman ✭✭
    edited 02/23/23
    Options

    I was able to accomplish this by following the video below. It creates a ranking system based on a auto number column, and then checks a column for anything that's less than rank 2, but if the duplicate column is checked. Using the date instead of a ranking system may have been easier as @Paulo Ferrer mentioned in the comments. Then it uses automation to move the columns that were checked for removal to a new sheet.

    Ranking formula: =IFERROR(RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], Barcode:Barcode, Barcode@row, Duplicate:Duplicate, 1), 1), "-")

    Remove check box formula: =IF(AND(Duplicate@row = 1, Rank@row < 2), 1, 0)

    Duplicate formula: =IF(COUNTIF(Barcode:Barcode, Barcode@row) > 1, 1)

    https://www.youtube.com/watch?v=i3DOqQXJXvk