CODEWITHSUNDEEP
excel
szakwani
szakwani

Reputation: 384

Searching Excel Data Set based on Between Dates

I Have two Data Sets as Follows

Data Set One

ID        Date From        Date To
5001      06/12/1999       08/1/2000
5001      08/08/2002       09/09/2004

Data Set Two

ID          Date From        Date To      Res
5001       01/01/1999       01/01/2001     E
5001       02/01/2001       01/01/2010     O

I would like to use the ID from Data Set one to find and return the Res based on the dates. The Current Employee Has been holding the position 5001 from the specified dates in Data Set One and I would like to Match it with Data Set Two to Understand The Position Res

Note: The Data Set greater than 10,000 Rows each

Upvotes: 0

Views: 18

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34180

To see if there was at least one "E" or "O" range on Sheet 2 that fully contained a range with the same ID on Sheet 1 I would use:-

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&B2,Sheet2!C:C,">="&C2,Sheet2!D:D,"E"),"E",IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&B2,Sheet2!C:C,">="&C2,Sheet2!D:D,"O"),"O",""))

starting in cell D2 of Sheet 1.

But to see if there was at least one range in Sheet 2 that overlapped with a range with the same ID on sheet 1 I would use:-

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&C2,Sheet2!C:C,">="&B2,Sheet2!D:D,"E"),"E",IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&C2,Sheet2!C:C,">="&B2,Sheet2!D:D,"O"),"O",""))

Upvotes: 1

Related Questions