Check if UID meets 2 criteria

[Darren Q]

Hi,

This one has me stumped!

I have a list of appointments for people (all of which have a unique ID). There are 2 services that they could have had an appointment with. Service A and B. I got as far as indicating if they have seen each service (e.g. Seen service A (yes, no), seen service B (yes, no). This is the bit I can’t work out. I have need to find the people who have seen service A and also service B. So check UID 001 - service A yes and yes for the same UID in service B.

At first I thought it would be very straightforward, if service A yes and service B yes...but each person could have several appointments so if I filter out the no’s from service A it rips out the yes’s in service B.

UID. Date. Service A. Service B
001. 1/1. Yes. No
001. 2/1. No. Yes
001. 3/1. No. Yes
002. 2/3. No. Yes
002. 3/3. No. Yes

From example above, only 001 has seen both services as 002 has only seen service B. Hope this explains it.

I Cheers

[6StringJazzer]

You explained what you're trying to figure out but not what you want your result to look like. Do you want another column for each row that says whether they have had both A&B, or do you want it in a completely different place, just listed by UIDs?

Suppose your data is in columns A, B, C, D. You have a list of UIDs somewhere in column M. In N:

=IF(AND(COUNTIFS(A:A,M1,C:C,"Yes")>0,COUNTIFS(A:A,M1,D:D,"Yes")>0),"BOTH","")

Or if you want to put in next to your existing data in column E

=IF(AND(COUNTIFS(A:A,A1,C:C,"Yes")>0,COUNTIFS(A:A,A1,D:D,"Yes")>0),"BOTH","")

Copy down.

[Darren Q]

That worked a treat and very logical.

Yes, I needed them in a column beside, so I took option B.

Only adjustment I had to make was to make it cell A2 instead of A1 because of my headers and add in a false statement.

I really appreciate the response. Thank you6StringJazzer

[6StringJazzer]

You are very welcome.

[Darren Q]

I had a few more practice goes there so the logic and method sticks. I was then able to knock out a simple pivot of the list of ID's who visited both.

Another occasion of me shouting at my screen "Of course! That makes sense now. Why didn't I think like that?"