Check previous values in transaction against current value

Dave Schierman

Greetings,

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".

I don't even know where to begin with this one.

Thanks

Dave Schierman

https://community.smartsheet.com/discussion/comment/271368#Comment_271368

Thank you for the reply Bassam. It does not appear to work, I may not have described what I’m looking for correctly.

The fields Box Number to Tag End are populated through form submission so I can’t replace the contents with a formula. I don’t need to compare Tag Start and Tag End for each Box on a specific row, in fact if the Opend field is Yes, I expect the Tag End to be different.

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 more sense. 

Bassam Khalil

Hi @Dave Schierman

Hope you are fine, i created sample as per your sheet and prepared the check formula for you, use in check column the following formula and convert it to column formula .

Check =IF(VALUE(RIGHT([Tag End]@row, 4)) - VALUE(RIGHT([Tag Start]@row, 4)) = 0, "Valid", "Not Valid")

and to copy Tag end for old entry from new entry use the following formula in Tag End:

Tag End =INDEX([Box Number]:[Tag Start], MATCH([Box Number]@row, [Box Number]:[Tag Start], 0), 2)

following screen shot show the result

Dave Schierman

https://community.smartsheet.com/discussion/comment/271368#Comment_271368

Thank you for the reply Bassam. It does not appear to work, I may not have described what I’m looking for correctly.

The fields Box Number to Tag End are populated through form submission so I can’t replace the contents with a formula. I don’t need to compare Tag Start and Tag End for each Box on a specific row, in fact if the Opend field is Yes, I expect the Tag End to be different.

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 more sense.