Filter, where certain line is missing

Uh, im a little puzzled by this one. Since i'm no Excel specialist, maybe someone of You has an open mind and an easy way to do this.

Basically, column A has phone numbers, column B has services associated with them. Since some numbers have more services, they have multiple occurences.

I want to show only phone numbers, where certain service doesnt exist (oh, and no duplicates)

I'm attaching a sample file, where i want to show only phone numbers, where "service 2" line is missing from.

Maybe reverse it somehow? Show numbers, where this service exists and then reverse somehow?

Any advice and guides are welcome

Thank You,
Rain

Not sure you can do this with Auto Filter.

One possibility would be to use formulae - using your sample file - and cell references are just for demo.

L3: Service 2
ie the Service of interest (so as to be dynamic going forward)

L4:
=SUMPRODUCT(($A$3:$A$29<>"")/COUNTIF($A$3:$A$29,$A$3:$A$29&""))-COUNTIF($B$3:$B$29,$L$3)
confirmed with Enter
gives you count of Cell nrs for which no Service 2 exists 
(assumes Service 2 is not repeated for any given cell number)

L5:
=IF(ROWS(L$5:L5)>$L$4,"",INDEX($A$3:$A$29,MATCH(1,($A$3:$A$29<>"")*ISNA(MATCH($A$3:$A$29&"^"&$L$3,$A$3:$A$29&"^"&$B$3:$B$29,0))*ISNA(MATCH($A$3:$A$29,$L$4:$L4,0)),0)))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied down to say L10

given use of L3 - if you alter the Service value you should find the other cells adapt accordingly

Originally Posted by qingpool

My problem is, i know i have 333 phone numbers without that service. In every result, the formula changes by 2 numbers (in the beginning and in the end). How do i copy that formula so many times with only these values changing

I'm afraid I don't really follow the above - could you perhaps post a further sample to illustrate ?

It won't be quick with very big data sets... how big is your real data set ?

Are you looking to process multiple "services" simultaneously or will you only ever be analysing one "service" at any given point in time ?

Is your dataset sorted or grouped together by Number & Service ?

perhaps
=IF(OR(A3="",SUMPRODUCT(($A$3:$A$30=A3)*($B$3:$B$30="service 2"))>0),"",IF(COUNTIF($A$3:A3,A3)=1,"filter me",""))

Uhh, i went afk meanwhile and my laptop went to sleep! On AC power!!! Calculations ruined..

Ok, did some some sorting and filtering. Now i have only 2 columns and 5500 rows of data.
There is about 1800 phone numbers, which makes about 3 lines of services per phone number. About 300 of them dont have the "Service 2" - these are the ones i need to list.

Applied the formula again, i dont know, now i calculates only about 1 number in 10 minutes. That cannot be right. My beard is growing faster.

martindwilson, i tried Your formula, but what should i substitute "filter me" with? Forgiveme for my ignorance.

Maybe there's a way to do this via multiple manual filterings and copy/paste?
Maybe delete every mention of the numbers which have "Service 2" on some row? And then remove duplicates from others?

Thank You

just drag down and auto filter on filter me or use a formula just to return the ones you want