Multiple criteria

[adambc]

I have a WB with 3 columns ...

CustomerID - one or many records depending on number of ServiceType

ServiceType - 10 possible values

ServiceEndDate

- I'm only interested in 4 of the 10 ServiceType values (let's call them A, B, C & D)
- I want to copy EVERY record for the CustomerID to another WS irrespective of ServiceType/ServiceEndDate
- IF all records with ServiceType A, B, C or D also have a ServiceEndDate (if ANY record has a ServiceType of A, B, C or D but DOES NOT have a ServiceEndDate then I don't want to do anything)

Is this possible?

Thanks ...

 

[Joe4]

I think you can do what you want using the new FILTER function.

Here is an example:

On the left is the data set, and on the right in red is the data you want returned.
The formula I used in cell F2 is this:

=FILTER(A2:C26,((B2:B26="A")+(B2:B26="B")+(B2:B26="C")+(B2:B26="D"))*(C2:C26>0))

 

[adambc]

I think you can do what you want using the new FILTER function.

Here is an example:
View attachment 118753

On the left is the data set, and on the right in red is the data you want returned.
The formula I used in cell F2 is this:

=FILTER(A2:C26,((B2:B26="A")+(B2:B26="B")+(B2:B26="C")+(B2:B26="D"))*(C2:C26>0))

Thanks ...

Two gotchas!

1. I need to do this in a macro (VBA)
2. CustomerID can have multiple ServiceType ie multiple records

But I will look at FILTER later ...

 

[Joe4]

1. I need to do this in a macro (VBA)

OK, you didn't mention this in your original request, but I have to ask why it has to be VBA?
Note that you can use VBA to enter in this formula.

2. CustomerID can have multiple ServiceType ie multiple records

I don't see why that should be any issue, unless there is something else related to this you aren't telling us.
The FILTER function simply returns any record that uses one of those 4 ServiceTypes and has a ServiceEndDate.

 

[adambc]

OK, you didn't mention this in your original request, but I have to ask why it has to be VBA?
Note that you can use VBA to enter in this formula.


I don't see why that should be any issue, unless there is something else related to this you aren't telling us.
The FILTER function simply returns any record that uses one of those 4 ServiceTypes and has a ServiceEndDate.

It’s a section in the middle of a longer macro - should have said in my post, but yes, I can use VBA to insert the FILTER formula …

But … I’d already got there with VBA - the piece it doesn’t handle (?) is checking ALL instances are satisfied - if any fail I need to exclude that CustomerID completely - but I think I’ve cracked it now by checking for a FALSE before doing anything else