Apr 14 2022 08:25 AM
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!
Apr 14 2022 08:58 AM
Solution
Here's a possible solution. It does take advantage of the FILTER function, which is new and requires the most recent version of Excel.
Apr 14 2022 09:11 AM
Apr 14 2022 12:12 PM
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.
Apr 14 2022 12:34 PM
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!
Apr 14 2022 02:57 PM
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,
(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.)
Apr 14 2022 03:07 PM
Apr 14 2022 03:28 PM
@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"))
Apr 14 2022 04:04 PM
Apr 14 2022 04:22 PM
Apr 15 2022 04:25 AM
Apr 15 2022 08:40 AM
@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.
Apr 15 2022 08:48 AM
Apr 15 2022 10:11 AM
Apr 15 2022 11:51 AM
Apr 14 2022 08:58 AM
Solution
Here's a possible solution. It does take advantage of the FILTER function, which is new and requires the most recent version of Excel.