How to get specific data from a list on sheet with most recent or higher level of issue?

Nick Amsler

So, I have a raw data sheet that shows each time an employee receives a type of correction within the organization. I need to somehow extract both the most recent and highest level they currently have based on only certain policies, and I have no idea how to build any formula to pull this needed information, here is an example of the raw data sheet:

So, I need to pull only certain policies (say a list of like 5-7 exact ones), the most recent of the highest level. I can create any extra sheet needed to organize this data, so I don't necessarily need to use this exact sheet.

Goal example: I need to pull into a couple fields on another sheet of what the highest level of corrective action for Armando for policy 118 and the date issued. Using this sheet as the goal would be to get "written/API" as it is a higher level of CA versus the verbal and the associated date would 11/27/22.

My first thought was a type of vlookup (if) function but can't figure it out. Would index/collect/match functions work better for what I am trying to get to?

I just have no idea how to structure either the data needed for an intermediate sheet, or the formula to pull. I would really appreciate any help with this issue and thank you in advance for taking the time to help me out on this. This community rocks!

Paul Newcome

You'll need a "Most Recent Date" column on the target sheet. Then you would use a MAX/COLLECT to pull in the most recent date for each Employee ID.

=MAX(COLLECT({Source Sheet Date Column}, {Source Sheet Emp ID Column}, @cell = [Employee ID]@row))

Then to pull in the data from the most recent, you would use an INDEX/COLLECT referencing the Employee ID and the "Most Recent Date" generated by that first formula.

=INDEX(COLLECT({Source Sheet SOP Column}, {Source Sheet Date Column}, @cell = [Most Recent Date]@row, {Source Sheet Emp ID Column}, @cell = [Employee ID]@row), 1)

For the highest level of corrective action, you would first need to insert a text/number helper column with a nested IF statement that will output different numbers for each action type.

=IF([Corrective Action]@row = "Lowest action possible", 1, IF([Corrective Action]@row = "2nd lowest action possible", 2, ....................))

Then you would go back to the target sheet and insert a "Highest Level" column. Then use a MAX/COLLECT similar to that first formula to pull in the highest level for each person, and finally an INDEX/COLLECT similar to the second formula to pull in the rest of the data.

Kimberly Loveless

Im not sure if this is the most effective way to do this but when I am looking for the most recent entry I use a suggestion that I found on here a few years ago.

First add an Auto-number column (any format).

Second add a text/number column called "Row #" with the formula...

=MATCH([Auto Number]@row,[Auto Number]:[Auto Number},0)

Then add a checkbox column with the formula...

=IF([Row #]@row=MAX(COLLECT([Row#]:[Row #],[SOP/POLICY]:[SOP/POLICY],[SOP/POLICY]@row)),1)

This will check the box of the most recent entry for the specific policy.

You could then even just set up a report that pulls just the most recent rows as indicated by the checkbox. For your scenario I would also group the report by that fist column (Name I think) so that it is easy to identify the most recent for each.

Hope that helps 😊

Nick Amsler

Hi @Kimberly Loveless ,

I really appreciate the help. This is what I have when I use what you suggested. Where would I put in the specific policies I would like to pull?

It seems the Row # simply pulls the same auto number column number. I did notice the check boxes end up being on box checked per policy with the max number of row # per policy. I feel like I am missing something, but I did put in the formulas as you suggested. Again, I really appreciate the help, this is definitely closer than where I was a couple hours ago!

Paul Newcome

You'll need a "Most Recent Date" column on the target sheet. Then you would use a MAX/COLLECT to pull in the most recent date for each Employee ID.

=MAX(COLLECT({Source Sheet Date Column}, {Source Sheet Emp ID Column}, @cell = [Employee ID]@row))

Then to pull in the data from the most recent, you would use an INDEX/COLLECT referencing the Employee ID and the "Most Recent Date" generated by that first formula.

=INDEX(COLLECT({Source Sheet SOP Column}, {Source Sheet Date Column}, @cell = [Most Recent Date]@row, {Source Sheet Emp ID Column}, @cell = [Employee ID]@row), 1)

For the highest level of corrective action, you would first need to insert a text/number helper column with a nested IF statement that will output different numbers for each action type.

=IF([Corrective Action]@row = "Lowest action possible", 1, IF([Corrective Action]@row = "2nd lowest action possible", 2, ....................))

Then you would go back to the target sheet and insert a "Highest Level" column. Then use a MAX/COLLECT similar to that first formula to pull in the highest level for each person, and finally an INDEX/COLLECT similar to the second formula to pull in the rest of the data.

Nick Amsler

Thanks @Paul Newcome !

Believe it or not, it was about an hour prior to your post I came to this conclusion:

I first set all policies that are driving related (the ones I needed to identify) into an "if" formula so I made a new column for that:

=IF([SOP/POLICY]@row = "203", "driving related", IF([SOP/POLICY]@row = "Drive Cam", "driving related", IF([SOP/POLICY]@row = "200.1", "driving related", IF([SOP/POLICY]@row = "209", "driving related", IF([SOP/POLICY]@row = "202", "driving related", IF([SOP/POLICY]@row = "206", "driving related", IF([SOP/POLICY]@row = "200.5", "driving related", IF([SOP/POLICY]@row = "211", "driving related", IF([SOP/POLICY]@row = "206.5", "driving related", IF([SOP/POLICY]@row = "207", "driving related", "---"))))))))))

On the target sheet I used a max formula to find the most recent date of the driving related policy:

=MAX(COLLECT({Corrective Action Data Sheet Range 6}, {Corrective Action Data Sheet Range 2}, [Employee ID]@row, {Corrective Action Data Sheet Range 5}, "driving related"))

then I gave up on the hierarchy idea because I couldn't figure it out, I just used a join formula to pull it all:

=JOIN(COLLECT({Corrective Action Data Sheet Range 4}, {Corrective Action Data Sheet Range 2}, [Employee ID]@row, {Corrective Action Data Sheet Range 5}, "driving related"), " , ")

giving me this ultimately:

If this needs more work on the highest level of CA, I will definitely use your suggestion on creating a new if formula to sort the CA's.

I really appreciate the help, there's is just so much to learn when it comes to organizing and moving data in certain ways to bring a different view or creating new data sets. Thank you again to you and to @Kimberly Loveless for the help!

Stay safe out there.