Pull most recent entry based on date comparing two sheets

Dakota Haeffner_N

Hello,

I am having an issue writing out this fromula and needed some assistance.

I need to write a formula that checks for an ID number titled MLL ID [ie. MLL00132] that is an auto number on the sheet I'm writing the formula on. I need it to look at two sheets, an open work and a complete work. Both sheets have a created date and modified date. I want it to return the person it is assigned to after checking both sheets to see which entry is the most recent. So likely if there is anything in the open work it will be the Open sheet but if nothing is going on with the entry it will be the completed sheet.

I hope this is enough information! Thanks!

Samuel Mueller

So the id number is not unique? and can you not assume that the one on the active sheet is not newer than the ones on the completed sheet?

To incorporate date you would need a index collect formula or join collect.. something like.

=iferror(index(collect({assignedRange}, {IDRange}, IDNumber@row, {Date},max(collect({Date}, {IDRange}, IDNumber@row)),1), index(collect({archiveAssignedRange}, {archiveIDRange}, IDNumber@row, {archiveDate},max(collect({archiveDate}, {archiveIDRange}, IDNumber@row)),1))

If you can't assume that the newest one would be on the active sheet first, then you would need to do something like this..

=iferror(index(collect({assignedRange}, {IDRange}, IDNumber@row, {Date},max(collect({Date}, {IDRange}, IDNumber@row),collect({archiveDate}, {archiveIDRange}, IDNumber@row)),1), index(collect({archiveAssignedRange}, {archiveIDRange}, IDNumber@row, {archiveDate},max(collect({archiveDate}, {archiveIDRange}, IDNumber@row),collect({Date}, {IDRange}, IDNumber@row)),1))

Dakota Haeffner_N

Basically we have a Master Library that is generating this MLL ID number and when items out of that Library are being worked on in this intake sheet they grab the auto generate number from the library and plug it into the request so the sheets can communicate with one another.

The MLL ID is unique in the library where I'm writing the formula and is usually only used once in the open intakes sheet but once we look at the completed sheet, one item from that library could be references several times.

I think I'm going to add a helper column to the completed sheet and then to add one to what you said, have it reference the Open sheet then the completed sheets helper columns. This will keep it clean rather than try to nest everything into the formula.

Samuel Mueller

Maybe something like this

=iferror(index({assignedRange}, match(IDNumber@row,{IDRange},0)), index({archiveAssignedRange}, match(IDNumber@row, {archiveIDRange},0))

Checks the active sheet first, if it's not there it checks the completed sheet.

Dakota Haeffner_N

@Samuel Mueller I see this working to pull the names but does this take in to account the most recent entry. There may be 1 on the open sheet that is brand new and 4 on the completed sheet that are older. In this case I want it to pull the most recent new one.

Similarly, if there is nothing in the open sheet and 5 in the completed sheet. How would it know which one is most recent?

Samuel Mueller

So the id number is not unique? and can you not assume that the one on the active sheet is not newer than the ones on the completed sheet?

To incorporate date you would need a index collect formula or join collect.. something like.

=iferror(index(collect({assignedRange}, {IDRange}, IDNumber@row, {Date},max(collect({Date}, {IDRange}, IDNumber@row)),1), index(collect({archiveAssignedRange}, {archiveIDRange}, IDNumber@row, {archiveDate},max(collect({archiveDate}, {archiveIDRange}, IDNumber@row)),1))

If you can't assume that the newest one would be on the active sheet first, then you would need to do something like this..

=iferror(index(collect({assignedRange}, {IDRange}, IDNumber@row, {Date},max(collect({Date}, {IDRange}, IDNumber@row),collect({archiveDate}, {archiveIDRange}, IDNumber@row)),1), index(collect({archiveAssignedRange}, {archiveIDRange}, IDNumber@row, {archiveDate},max(collect({archiveDate}, {archiveIDRange}, IDNumber@row),collect({Date}, {IDRange}, IDNumber@row)),1))

Dakota Haeffner_N

Basically we have a Master Library that is generating this MLL ID number and when items out of that Library are being worked on in this intake sheet they grab the auto generate number from the library and plug it into the request so the sheets can communicate with one another.

The MLL ID is unique in the library where I'm writing the formula and is usually only used once in the open intakes sheet but once we look at the completed sheet, one item from that library could be references several times.

I think I'm going to add a helper column to the completed sheet and then to add one to what you said, have it reference the Open sheet then the completed sheets helper columns. This will keep it clean rather than try to nest everything into the formula.

Samuel Mueller

That makes a lot more sense, and that sounds like a good approach. Having the information on two separate sheets makes it hard to accomplish a collect type of formula because you can't combine the ranges, so you have to basically get the max date of each sheet, and compare the max between those two with the unique ids to return the correct entry. Not a straightfoward process!

Another option, maybe not as pretty, but you could copy rows from both sheets to a third sheet based on a date change, and then you would only have to reference one sheet for the latest date. It would collect a lot of rows, but you would really only use it to easily determine which one has the latest date and return the assigned to. assuming that the assigned to doesn't change unless a date does.