Formula to make list shorter

[Yaseraliakram]

Hi guys,

I was wondering if somebody could help me with the following.
I have three columns, if somebody dials 93xxxxxxxx numbers the rate wil be charged 0.1575, and if someone dials 9320xxxxx the rate will also be charged0.1575.
In this case i want to delete the row with the prefix 9320. how ever the rate of 9325 is different so that i dont want to delete.
The same goes for 937500 should be deleten beceause if that prefix is not in the list the rate of the prefix 937 will be charged.

Can i do this witha formula ? See the excel list below.

Afghanistan	93	0.1575
Afghanistan Kabul	9320	0.1575
Afghanistan Kabul	9325	0.16
Afghanistan Mobile	937	0.11172
Afghanistan Mobile AT	9375	0.15382
Afghanistan Mobile AT	937500	0.15382
Afghanistan Mobile AWCC	9370	0.13472
Afghanistan Mobile AWCC	9371	0.13472
Afghanistan Mobile AWCC	93711	0.13472
Afghanistan Mobile Etisalat	9378	0.11172
Afghanistan Mobile MTN	9376	0.11413
Afghanistan Mobile MTN	9377	0.11413
Afghanistan Mobile Roshan	9372	0.10238
Afghanistan Mobile Roshan	9379	0.10238
Afghanistan Mobile Salam	9374	0.13692

<colgroup><col style="mso-width-source:userset;mso-width-alt:9113;width:200pt" width="267"> <col style="mso-width-source:userset;mso-width-alt:3003;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2252;width:50pt" width="66"> </colgroup><tbody>
</tbody>

 

[Yaseraliakram]

great help man, working fine thnx

 

[jasonb75]

A slightly shorter formula for you for future use if you need it.

=SUMPRODUCT(COUNTIFS($B$2:$B$16,INT(B2/({10}^COLUMN($A$1:$L$1))),$C$2:$C$16,C2))

Rows with a zero result need to be kept, those with a result greater than 0 can be removed.

 

[Yaseraliakram]

Yes this formula is also working awsome,
Thanx a million again,

how ever i have a new chalange, maybe you can solve that also, as it looks similiar with this one.

compare and find prize through prefix

Hi guys I was wondering if someone could help me with this challenage. I am looking for a formula to calculate a prize based on the prefix (telephonenumber breakout). These breakouts are to call international. See below the prefix and rate example template. It works like this, if the prefix is...

www.mrexcel.com