most
Board Regular
- Joined
- Feb 22, 2011
- Messages
- 106
- Office Version
- 365
- 2019
- Platform
- Windows
- Mobile
Hi,
I would like some input/pointers on how to solve this, I'm not sure where to begin...
I want to write Active, Not active or Ignore in column E based on three criteria.
As you can see in the table, a person can occur 2-3 times, even 6 times in my orginal data. Data is sorted on column A.
If same employment (column B) occurs two times = all rows should be Ignore.
If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then Active else Not active.
/Marcus
<tbody>
</tbody>
I would like some input/pointers on how to solve this, I'm not sure where to begin...
I want to write Active, Not active or Ignore in column E based on three criteria.
As you can see in the table, a person can occur 2-3 times, even 6 times in my orginal data. Data is sorted on column A.
If same employment (column B) occurs two times = all rows should be Ignore.
If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then Active else Not active.
/Marcus
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Person | Employment | StartDate | EndDate | Expected result |
2 | 194505 | 02 | 2019-04-24 | 2019-11-01 | Active |
3 | 194505 | 01 | 2013-06-17 | 2019-03-29 | Not active |
4 | 184810 | 02 | 2016-01-01 | 2019-10-01 | Ignore |
5 | 184810 | 02 | 2016-01-01 | 2019-10-01 | Ignore |
6 | 175007 | 03 | 2019-07-23 | 2019-12-31 | Ignore |
7 | 175007 | 02 | 2018-01-01 | 2019-06-30 | Ignore |
8 | 175007 | 02 | 2018-01-01 | 2019-06-30 | Ignore |
9 | 166109 | 05 | 2019-03-11 | 2019-12-31 | Active |
10 | 166109 | 04 | 2019-01-01 | 2019-03-10 | Not active |
11 | 166109 | 03 | 2018-09-10 | 2018-12-31 | Not active |
12 | |||||
13 | |||||
14 | |||||
15 | |||||
16 | |||||
17 | 2019-09-19 |
<tbody>
</tbody>