How to solve a issue with three criterias

[most]

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

	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>

 

[most]

You have given no logic as to why they should be Ignore.

Ignore is used to point out to the user that those lines need to be double checked, in the final output there should only be one line and one line only per "Person", the idea is that those which are "Ignored" are manually checked and switched to "Active" or "Not Active", the final output should then have one "Active" per "Person".

Row 12
In post 6 you said "EndDate can be blank were expected result should be Active."

Row 13
Post 1 "If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then Active"

Rows 14 & 15
Post 1 "If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then ... else Not active."

That statement remains, problem with line 12/13 is that both are Active and therefore needs to be checked manually, as in my comment above.
Problem with line 14/15 is that none are Active and therefore needs to be checked manually, as in my comment above.

Further, the condition for Ignore was "If same employment (column B) occurs two times = all rows should be Ignore.". None of rows 12:15 meet that condition.

It was when I first got your help I realized all different scenarios. I believe and hope my first comment in this post explains what I trying to accomplish.

 

[Peter_SSs]

Ignore is used to point out to the user that those lines need to be double checked,

Sorry, but that sounds completely contradictory to me. Ignore = Please Check ?


OK, ignoring the above ..

in the final output there should only be one line and one line only per "Person", ..... the final output should then have one "Active" per "Person".

.. could we add an extra column like this?
If so, it could be added to the code to do it.

Excel Workbook
	A	B	C	D	E	F
1	Person	Employment	StartDate	EndDate	Result	Check?
2	194505	2	2019-04-24	2019-11-01	Active
3	194505	1	2013-06-17	2019-03-29	Not Active
4	184810	2	2016-01-01	2019-10-01	Ignore	Check
5	184810	2	2016-01-01	2019-10-01	Ignore	Check
6	175007	3	2019-07-23	2019-12-31	Ignore	Check
7	175007	2	2018-01-01	2019-06-30	Ignore	Check
8	175007	2	2018-01-01	2019-06-30	Ignore	Check
9	166109	5	2019-03-11	2019-12-31	Active
10	166109	4	2019-01-01	2019-03-10	Not Active
11	166109	3	2018-09-10	2018-12-31	Not Active
12	156108	2	2019-08-01		Active	Check
13	156108	1	2018-12-01	2019-10-31	Active	Check
14	256118	4	2019-05-06	2019-08-30	Not Active	Check
15	256118	3	2018-08-20	2019-05-05	Not Active	Check
Activity (4)

 

[most]

Yes, that will work. Thanks for all the help!

 

[Peter_SSs]

Yes, that will work. Thanks for all the help!

OK, so if you want the code to do that for you, add the blue section where shown.

  Range("E2").Resize(UBound(a)).Value = Application.Index(a, 0, 5)
  With Range("F2").Resize(UBound(a))
    .Formula = "=IF(COUNTIFS(" & .Offset(, -5).Address & ",A2," & .Offset(, -1).Address & ",""Active"")=1,"""",""Check"")"
    .Value = .Value
  End With
End Sub