Formula to show most recent submission within a shift and a checkbox

I've got a sheet where forms are submitted by QR code when bins are checked each shift and I want the most recent submission for each shift to get a 1 in the checker column. I've got an automation scheduled for each day to move the most recent submission for each shift to another sheet to be kept at the end of the day(pulling all rows in the checker column with a 1), and the other entries are deleted. I got this to work at one point, but the deletions got rid of the formula and I've forgotten what all was included. The activity log had a formula, but it wasn't the one that worked (hence the unparseable). The formula needs to check if the bin is checked, if it is shift 1 or 2, and to pull the highest number from the autonumber column.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Are you wantign to flag the most recent entry for each shift or for the most recent entry for each bin for each shift?

Speerj

@Paul Newcome Thank you for response, after digging around some more I finally found a post I referenced when making the formula and was able to recreate it.

Paul Newcome

Glad you were able to get it sorted. What was your final formula?

If just going to the shift, I would have used somethign along the lines of

=IF(Autonumber@row = MAX(COLLECT(Autonumber:Autonumber, Date:Date, @cell = Date@row, Shift:Shift, @cell = Shift@row)), 1)

If needing to include the bins, I would have inserted a helper column called "String" with this column formula:

=Date@row + "" + Shift@row + JOIN([Bin 1]@row:[Bin 10]@row)

Then I would have used a similar IF to flag the most recent.

=IF(Autonumber@row = MAX(COLLECT(Autonumber:Autonumber, String:String, @cell = String@row)), 1)

Speerj

So it was needing to check each bin for each shift. The formula ended up being

=IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 1]:[Bin 1], @cell = [Bin 1]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 1]:[Bin 1], @cell = [Bin 1]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 2]:[Bin 2], @cell = [Bin 2]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 2]:[Bin 2], @cell = [Bin 2]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 3]:[Bin 3], @cell = [Bin 3 ]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 3]:[Bin 3], @cell = [Bin 3]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 4]:[Bin 4], @cell = [Bin 4]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 4]:[Bin 4], @cell = [Bin 4]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 5]:[Bin 5], @cell = [Bin 5]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 5]:[Bin 5], @cell = [Bin 5]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 6]:[Bin 6], @cell = [Bin 6]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 6]:[Bin 6], @cell = [Bin 6]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 7]:[Bin 7], @cell = [Bin 7]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 7]:[Bin 7], @cell = [Bin 7]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 8]:[Bin 8], @cell = [Bin 8]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 8]:[Bin 8], @cell = [Bin 8]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 9]:[Bin 9], @cell = [Bin 9]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 9]:[Bin 9], @cell = [Bin 9]@row , Shift:Shift, "2")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 10]:[Bin 10], @cell = [Bin 10]@row , Shift:Shift, "1")), 1, IF([Autonumber]@row = MAX(COLLECT([Autonumber]:[Autonumber], [Bin 10]:[Bin 10], @cell = [Bin 10]@row , Shift:Shift, "2")), 1))))))))))))))))))))

Paul Newcome

Ah. Ok. That's quite a bit of formula there, but I do see what it is doing. It is basically checking each bbin individually per shift in a series of nested IFs. I personally would still go with what I mentioned above using the "String" column, but as long as you have something that is working, there is no real need to change anything.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.