How can I filter out the data by using the conditions on multiple fields?

[Hangxing_Zhou]

I have a huge data volume table, I need work out the total count number by a pattern.

Sample table fields and records:

Unit_Name    Message_Name  Time_Of_Message(YY/MM/DD/HH/MM)

Unit_A           Message_B          2020-12-01-1:00

Unit_A           Message_C          2020-12-01-1:10

Unit_A           Message_B          2020-12-01-1:20

Unit_A           Message_C          2020-12-01-1:30

Unit_A           Message_B          2020-12-01-1:00

Unit_A           Message_B          2020-12-01-1:00

Unit_A           Message_B          2020-12-01-1:00

Unit_B           Message_B          2020-12-01-1:20

Unit_B           Message_A          2020-12-01-1:22

Unit_B           Message_B          2020-12-01-1:25

Unit_B           Message_B          2020-12-05-2:20

Unit_B           Message_A          2020-12-05-2:20

Unit_B           Message_B          2020-12-05-2:21

...

...

...

My requirements as below:

1. Remove All the records of the Unit_Name   if the Unit_Name does not include Message_A(I have sorted data by Unit_Name and Time_of_Message)

    For Example, remove all records for Unit_A as above

2. Count the total number and group by Unit_Name for all "Time_of_Message" of "Message_B" is bigger than "Time_of_Message" of "Message_A", and within 5mins

   For example, Unit_B: 2

I tried some formulas, but seems doesn't work.

Thanks!

[BretCarr]

I read your request at least 5 times and I'm still not sure what you are wanting to do. Can you show how the shown data (all of it) would be reformatted if everything you want to happen comes true?

Thanks!

[Hangxing_Zhou]

The data set grouped by "Unit_Name" - events happened on the unit separately by month, the requirements:

If no specific event(Message_A) happened on the unit in the month, I need remove all the records of the Unit from the table.

If the specific event(Mesage_A) happened on with the unit, then I need combine another event(Message B）by the "time_field" (diff time <5mins), message B happened later on message A. I need count the total number of this pattern for each unit by Month. 

[Qiu · Accepted solution]

@Hangxing_Zhou 
I think I get your intention here, hopefully.

[Hangxing_Zhou]

Thanks Qiu, It works  for my requirements after changed a little bit.