How to solve a issue with three criterias

most

most

Board Regular
Joined
Feb 22, 2011
Messages
107
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. 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

ABCDE
Expected result
Active
Not active
Ignore
Ignore
Ignore
Ignore
Ignore
Active
Not active
Not active

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00B050"]Person[/TD]
[TD="bgcolor: #00B050"]Employment[/TD]
[TD="bgcolor: #00B050"]StartDate[/TD]
[TD="bgcolor: #00B050"]EndDate[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]194505[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-04-24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-11-01[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]194505[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2013-06-17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-03-29[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]184810[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2016-01-01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2019-10-01[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]184810[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2016-01-01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2019-10-01[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]175007[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]03[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-07-23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-12-31[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]175007[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2018-01-01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-06-30[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]175007[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]02[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2018-01-01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]2019-06-30[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]166109[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]05[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2019-03-11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2019-12-31[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]166109[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2019-01-01[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2019-03-10[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]166109[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]03[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2018-09-10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CDFFCD]#CDFFCD[/URL] , align: right"]2018-12-31[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"]2019-09-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
 
Sort by date Sort by votes
You have given no logic as to why they should be Ignore.
Click to expand...
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."
Click to expand...
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.
Click to expand...
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.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
most said:
Ignore is used to point out to the user that those lines need to be double checked,
Click to expand...
Sorry, but that sounds completely contradictory to me. Ignore = Please Check ? :huh:


OK, ignoring the above ..
most said:
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".
Click to expand...
.. could we add an extra column like this?
If so, it could be added to the code to do it.

Excel Workbook
ABCDEF
1PersonEmploymentStartDateEndDateResultCheck?
219450522019-04-242019-11-01Active 
319450512013-06-172019-03-29Not Active
418481022016-01-012019-10-01IgnoreCheck
518481022016-01-012019-10-01IgnoreCheck
617500732019-07-232019-12-31IgnoreCheck
717500722018-01-012019-06-30IgnoreCheck
817500722018-01-012019-06-30IgnoreCheck
916610952019-03-112019-12-31Active
1016610942019-01-012019-03-10Not Active
1116610932018-09-102018-12-31Not Active
1215610822019-08-01ActiveCheck
1315610812018-12-012019-10-31ActiveCheck
1425611842019-05-062019-08-30Not ActiveCheck
1525611832018-08-202019-05-05Not ActiveCheck
Activity (4)
 
Upvote 0
Yes, that will work. Thanks for all the help!
 
Upvote 0
most said:
Yes, that will work. Thanks for all the help!
Click to expand...
OK, so if you want the code to do that for you, add the blue section where shown.
Rich (BB code): 
  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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
mrshuck
M
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
335
JMWh1t3
J
P
  • Question Question
Excel pivot with milestones across the various dates to show as a months on the left and a count of that milestone on the right
Replies
1
Views
417
POLACOLOCO
P

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top