craigexcel
Active Member
- Joined
- Jun 28, 2006
- Messages
- 298
- Office Version
- 2016
- Platform
- Windows
I'm using Office 365 @ work.
I'm working with some HR data, and have created a formula that returns a subset of the data, i.e. those employees with "Vacate" in the "Job Profile" field, since I want to keep a running list of 'vacated' employees on a year-to-date basis, instead of requesting some kind of ad hoc report from HR, which is already overwhelmed. I download the current week's source data every Monday (so always a Monday date in this field), and drop into an Excel table.
The formula shown is what I've come up with to get the subset of ONLY employees with the "Vacate" text. As can be seen in the sample data, there can be more than 1 record for a given employee, depending on when it became known the employee would be leaving. What I really need in the output, though, is only a single record for each employee in this subset group, for ONLY the most recent date in the "system rpt date" field, as shown in the 2nd set of data. (Note that while I left the MAX part of the formula in place, it doesn't do what I'm looking for. I left it in place since it doesn't seem to affect the output {tho not exactly what I'm looking for}, and as a prompt for me that MAX would likely be needed, just in a different way in the formula.) Also, have tried a few iterations of the "source!A2:A500000" to get that "system rpt date" data to be returned as 'm/d/yyyy', AND actually be a number as opposed to text, but haven't been successful. Still have had to manually format to display as 'm/d/yyyy'. Though I suppose leaving it as text may be ok, since I don't think there will be a need to use those values for further [numeric] lookups or anything like that.
It seems FILTER would be a key element of the formula, but with a number of iterations using FILTER, UNIQUE, INDEX / MATCH, SORT, I haven't hit on the right combination yet. The 1st set of data shows a generic sample of key fields I'm working with. The 2nd set shows what I'm looking for in the results.
I also just saw this post -- Filter in multiple areas -- where Joe4 suggested using VSTACK for a similar type of situation. Couldn't get further with that, since I'm still unable to figure out the MAX part (assuming that's what should be used) to return ONLY the most recent date for each employee.
Any suggestions as to how to adjust the formula to return the desired results? I appreciate your time at having a look at this.
=SORT(FILTER(CHOOSE({1,2,3,4,5,6},source!I2:I500000,source!B2:B500000,source!D2:D500000,source!C2:C500000,VALUE(TEXT(source!A2:A500000,"m/d/yyyy")),source!G2:G500000),(MAX(source!A2:A500000)*(ISNUMBER(SEARCH("Vacate",source!G2:G500000))))),{1,2},1,FALSE)
sample data subset, from FILTER function formula:
expected results:
I'm working with some HR data, and have created a formula that returns a subset of the data, i.e. those employees with "Vacate" in the "Job Profile" field, since I want to keep a running list of 'vacated' employees on a year-to-date basis, instead of requesting some kind of ad hoc report from HR, which is already overwhelmed. I download the current week's source data every Monday (so always a Monday date in this field), and drop into an Excel table.
The formula shown is what I've come up with to get the subset of ONLY employees with the "Vacate" text. As can be seen in the sample data, there can be more than 1 record for a given employee, depending on when it became known the employee would be leaving. What I really need in the output, though, is only a single record for each employee in this subset group, for ONLY the most recent date in the "system rpt date" field, as shown in the 2nd set of data. (Note that while I left the MAX part of the formula in place, it doesn't do what I'm looking for. I left it in place since it doesn't seem to affect the output {tho not exactly what I'm looking for}, and as a prompt for me that MAX would likely be needed, just in a different way in the formula.) Also, have tried a few iterations of the "source!A2:A500000" to get that "system rpt date" data to be returned as 'm/d/yyyy', AND actually be a number as opposed to text, but haven't been successful. Still have had to manually format to display as 'm/d/yyyy'. Though I suppose leaving it as text may be ok, since I don't think there will be a need to use those values for further [numeric] lookups or anything like that.
It seems FILTER would be a key element of the formula, but with a number of iterations using FILTER, UNIQUE, INDEX / MATCH, SORT, I haven't hit on the right combination yet. The 1st set of data shows a generic sample of key fields I'm working with. The 2nd set shows what I'm looking for in the results.
I also just saw this post -- Filter in multiple areas -- where Joe4 suggested using VSTACK for a similar type of situation. Couldn't get further with that, since I'm still unable to figure out the MAX part (assuming that's what should be used) to return ONLY the most recent date for each employee.
Any suggestions as to how to adjust the formula to return the desired results? I appreciate your time at having a look at this.
=SORT(FILTER(CHOOSE({1,2,3,4,5,6},source!I2:I500000,source!B2:B500000,source!D2:D500000,source!C2:C500000,VALUE(TEXT(source!A2:A500000,"m/d/yyyy")),source!G2:G500000),(MAX(source!A2:A500000)*(ISNUMBER(SEARCH("Vacate",source!G2:G500000))))),{1,2},1,FALSE)
sample data subset, from FILTER function formula:
Company | employee ID | Last Name | First Name | system rpt date | Job Profile |
Comp1 | 101 | Jones | Bob | 45103 | job A (Position Vacate: 07/06/2023) |
Comp1 | 101 | Jones | Bob | 45110 | job A (Position Vacate: 07/06/2023) |
Comp1 | 145 | Smith | Ron | 45110 | job B (Position Vacate: 07/06/2023) |
Comp1 | 152 | Wilson | Jack | 45117 | job C (Position Vacate: 07/14/2023) |
Comp2 | 136 | Anderson | Bill | 45103 | job A (Position Vacate: 07/21/2023) |
Comp2 | 188 | Smith | James | 45124 | job D (Position Vacate: 07/21/2023) |
Comp2 | 205 | Matson | Tom | 45117 | job E (Position Vacate: 07/19/2023) |
Comp2 | 205 | Matson | Tom | 45124 | job E (Position Vacate: 07/19/2023) |
expected results:
Comp1 | 101 | Jones | Bob | 45110 | job A (Position Vacate: 07/06/2023) |
Comp1 | 145 | Smith | Ron | 45110 | job B (Position Vacate: 07/06/2023) |
Comp1 | 152 | Wilson | Jack | 45117 | job C (Position Vacate: 07/14/2023) |
Comp2 | 136 | Anderson | Bill | 45103 | job A (Position Vacate: 07/21/2023) |
Comp2 | 188 | Smith | James | 45124 | job D (Position Vacate: 07/21/2023) |
Comp2 | 205 | Matson | Tom | 45124 | job E (Position Vacate: 07/19/2023) |