Compare values of an item in one row against previous instance of item

Dave Schierman

I have a sheet that collects transaction via a form. Each new transaction is added to the top row so I can pull vlookup to populate various other sheets. I'm trying find a formula that will compare entered transactions against prior entry of the same item. Using example sheet shown, if the "Tag Start" value for "Box 1" is different than the "Tag End" value of the previous entry for "Box 1" , I want to return a "Not Valid" response in the "Check" column, otherwise show a "Valid".

Workflow wise, someone checks out a Box which is sealed with a serialized tag. If they never need to break the tag to open the box, then the same tag will be on the box when returned. If they do open the box, then they place a new serialized tag on it. So, on a single transaction row, the tag start and tag end numbers may or may not be different depending on if they ended up replacing the serialized tag. The formula I’m looking for to check and make sure that the serialized tag has not been changed between when the box was turned in previously and checked out now. i.e. If Box 1 was turned in two weeks ago with the number G2928 in the Tag Out field, I want to make sure that when it was checked out yesterday, it still has G2928 as the Tag Start number.  Hopefully that makes sense.

Leibel S

@Dave Schierman

Try the below setup.

Add the below 2 columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

In your check column add the below formula:

=IF(INDEX([Tag end]:[Tag end],MIN(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], >[ROW#]@row, [Box Number]:[Box Number], [Box Number]@row)))=[Tag Start]@row, "Valid", "Not Valid")

(Edited column names to match yours)

Leibel S

@Dave Schierman

It is getting an error because of [Box Number] column when it is the first time the item shows up.

try the below instead:

=IFERROR(IF(INDEX([Tag end]:[Tag end],MIN(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], >[ROW#]@row, [Box Number]:[Box Number], [Box Number]@row)))=[Tag Start]@row, "Valid", "Not Valid"),"FIRST ENTRY")

You can replace the bolded part with the message you want to show...

Leibel S

@Dave Schierman

Try the below setup.

Add the below 2 columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

In your check column add the below formula:

=IF(INDEX([Tag end]:[Tag end],MIN(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], >[ROW#]@row, [Box Number]:[Box Number], [Box Number]@row)))=[Tag Start]@row, "Valid", "Not Valid")

(Edited column names to match yours)

Dave Schierman

@Leibel S !! Brilliant. That works. Thank you so much.

Dave Schierman

@Leibel S One question, when I convert the formula to a column formula I get an #INVALID OPERATION. Does this only work on the top row?

Leibel S

@Dave Schierman

It is getting an error because of [Box Number] column when it is the first time the item shows up.

try the below instead:

=IFERROR(IF(INDEX([Tag end]:[Tag end],MIN(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], >[ROW#]@row, [Box Number]:[Box Number], [Box Number]@row)))=[Tag Start]@row, "Valid", "Not Valid"),"FIRST ENTRY")

You can replace the bolded part with the message you want to show...

Dave Schierman

@Leibel S Excellent. Thank you so much