Comparing time slots and events

Copper Contributor

Hi, 

I have a problem that I cannot solve with my relatively moderate vlookup skills. I have two sheets of data, and the common factor between these is the peoples ID numbers.

 

The first sheet is a list of events. The columns are the persons ID-number and the date when the event happened. A individual may have multiple events.

 

The second sheet is a list of time slots. The columns are the persons id number, starting day of the time slot and the ending day of the times slot. A individual may have multiple time slots. 

 

The problem that I am trying to solve, is whether any of the events have happened during any of the time slots for that specific individual. The issue that I encounter is, that for every individual there are multiple rows whit different time slots and for every individual there are multiple events, which leads to the problem that lookup functions picks only the first ones. 

 

I hope someone finds this interesting and knows a brilliant solution!


Best regards!

 

1 Reply

@tulkkika 

One way to solve this problem is to use a combination of the SUMPRODUCT and COUNTIFS functions. You can use the SUMPRODUCT function to count the number of times an individual’s ID appears in both sheets. Then, you can use the COUNTIFS function to count the number of events that fall within each time slot for that individual.

 

Here’s an example formula that you can use in the second sheet to check if any events have happened during any of the time slots for a specific individual:

=SUMPRODUCT((Sheet1!A:A=A2)*(Sheet1!B:B>=B2)*(Sheet1!B:B<=C2))>0

This formula assumes that Sheet1 contains the list of events, with column A containing the person’s ID number and column B containing the date of the event. It also assumes that Sheet2 contains the list of time slots, with column A containing the person’s ID number, column B containing the starting day of the time slot, and column C containing the ending day of the time slot.

The formula checks if the sum of products of three conditions is greater than 0.

The first condition (Sheet1!A:A=A2) checks if the person’s ID number in Sheet1 matches the ID number in cell A2 of Sheet2.

The second condition (Sheet1!B:B>=B2) checks if the date of the event in Sheet1 is greater than or equal to the starting day of the time slot in cell B2 of Sheet2.

The third condition (Sheet1!B:B<=C2) checks if the date of the event in Sheet1 is less than or equal to the ending day of the time slot in cell C2 of Sheet2.

If all three conditions are met for at least one event, then the formula will return TRUE, indicating that at least one event has happened during one of the time slots for that individual.

Otherwise, it will return FALSE.

 

You can then drag this formula down to fill the rest of column D with TRUE or FALSE values indicating whether any events have happened during any of the time slots for each individual.

 

I hope this helps! …if not, please insert a file (without sensible data) and explain step by step what you like to accomplish.