SOLVED

autofill date a training was completed by item/person

Copper Contributor

Hello, how are you doing?
I have been trying to do something in excel for a few days and I have not figured out a formula that works well.

On sheet 1, column A, i have a big list of training items. On the same row for each item in this column, i have USER NAME, TRAINING NAME, COMPLETION DATE.
In this sheet 1, i have the same USER NAME repeated several times, as one person may have completed more than one training.

On sheet 2, column A, i have USER NAME (this time without duplicates) followed by TRAINING ITEM/Completion date.
I want to come up with a formula where excel goes through sheet 1, and autofill the date which the training was completed based on the training item for each person on the list.

i have tried IF formulas without success (SPILL error).

Can you recommend me a way for me to do this? A function i could look up and learn?
This problem is killing me.
I have attached a picture for an example.
Thank you very much for your help!

ex1.PNGex2.PNG

14 Replies
best response confirmed by ZoroBK (Copper Contributor)
Solution

@ZoroBK 

 

Here's a possible solution. It does take advantage of the FILTER function, which is new and requires the most recent version of Excel.

 

mathetes, thank you very much for your time and effort.

I believe that this solution will work. I will be trying it in the next couple of hours and I will let your know. I want to be able to fully understand the formula so I can use it well.

Also, the "initial" part is a different training, like phase 1 and phase 2. In my example, no one completed the "initial" part of the training.

Once again, thank you. I truly appreciate that you spared some time to help me out.

@ZoroBK 

 

This is the key formula

=FILTER(Report!$C$2:$C$5,(Report!$A$2:$A$5=Sheet2!C$2)*(Report!$B$2:$B$5=Sheet2!$A3))

 

What this does is filter (from the table in the Report tab)  the date column where the course equals the course named in row 2, the name equals the name in column A, and just display the date (which is in column C of the "Report" tab. This works in this case--and the multiple criteria can be used--because there's by definition only one date that is in the intersection of all those criteria. Otherwise, FILTER delivers a "dynamic array"--often multiple rows. 

 

Since "Initial" is also a course, now that you've clarified that, you can simply copy the formula from one of the adjacent cells into that column.

 

Here's a video from which you can learn about the full power of FILTER. It's a relatively new function.

https://www.youtube.com/watch?v=9I9DtFOVPIg

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...

@mathetes 

Thank you very much for another thoughful reply.

on my "real" spreadsheet, i have people that have completed the same training twice on different dates.

is there away for us to display two dates in the same cell using the FILTER formula?

Or is there a better way to go about it?

 

Thank you!

@ZoroBK 

 

You wrote:

on my "real" spreadsheet, i have people that have completed the same training twice on different dates.

is there away for us to display two dates in the same cell using the FILTER formula?

Or is there a better way to go about it?

 

So let me ask,

  1. first, is there a valid reason why people might complete the same training twice? "Valid" not including, in my mind at least, "they failed the first time so took it again".  In that case, I would think you'd only care about the final completion.
  2. However, if the data source is recording the failed attempt (or whatever the reason for there being multiple "completions" then I have another question: If they have indeed completed it twice (whatever the reason) do you WANT or NEED to display both dates in this summary? That does present its own complications, but if there's a reason, so be it. And no, it would not really be practical to display two dates in the same cell. It would be possible, but especially if it's the exception, it would be a case of the tail wagging the dog to make provision for it in all cases.

 

(By the way, personally I would think that only the most recent would matter for most purposes. Let the master database contain all the raw data, but a summary needn't get into the weeds that much.)

 

 

I see what you mean. We had an issue with our training program, and that is why some people have repeated the training.
If a person completed the same training on different days, how could I go about deleting the older entries and only keeping the most recent one? Which formula could i use?
Thank you!

@ZoroBK Your sheet contains two tabs. What's he relationship between the two? I've been assuming one is "raw data"; the other a meaningful summary of the useful info. As a former HR database manager, I'd loathe to just delete valid data...if an event happened, and then was superseded, it's just superseded, but not deleted.

 

So you'd be looking for the maximum date which is accomplished by nesting the formula you already have in MAX

=MAX(FILTER(Report!$C$2:$C$5,(Report!$A$2:$A$5=Sheet2!C$2)*(Report!$B$2:$B$5=Sheet2!$A3),"No date found"))

 

I have only left the most currents dates now. However, whenever i apply the FILTER formula, it only works for the first few items. For the rest i get a calculation error (#CALC!).
At first i thought it was my mistake, however i get the same error when i copy your formula.
The filter only worked for the first rows, nothing else.
I can make it work the the most current date, and leave the raw data as an extra sheet so we can refer to.
Thank you!
To clarify, I receive an "Empty Array" error.
The formula as I wrote it only addresses the first few rows, the ones that existed in your sample sheet. You need to extend it. Better yet, make sure the source data is in the form of an Excel table.

@mathetes I am aware, and I have changed the formula. However, i can never get it to work. I always get empty array. I am not sure what i am doing wrong with the formula. I have attached a couple of screenshots. 

excel1.jpgexcel2.png

NEVERMIND, I TRIED 5 TIMES AND I GOT IT TO WORK!
I manage to get it to work yesterday, however it gave me random numbers. Upon further inspection, i learned that Excel stores dates as odd looking numbers. You have to convert those odd looking numbers.
Thank you very much for your help Mathetes!
I could not manage to get the MAX and FILTER nested. I always get zero. I found an un-elegant way of getting the most recent date, however, it would be cool if i could get the max formula to work.
Are you able to attach a file here on these boards (without real names)? I'd be happy to take a look.

If not here, maybe in OneDrive or some place similar. Or send me a personal message here at the microsoft site...click on my name and select "Message" up at the top right corner; you can attach a file there that only I will see.
1 best response

Accepted Solutions
best response confirmed by ZoroBK (Copper Contributor)
Solution

@ZoroBK 

 

Here's a possible solution. It does take advantage of the FILTER function, which is new and requires the most recent version of Excel.

 

View solution in original post