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 Kabul

Afghanistan Kabul

Afghanistan Mobile

Afghanistan Mobile AT

Afghanistan Mobile AT

Afghanistan Mobile AWCC

Afghanistan Mobile AWCC

Afghanistan Mobile AWCC

Afghanistan Mobile Etisalat

Afghanistan Mobile MTN

Afghanistan Mobile MTN

Afghanistan Mobile Roshan

Afghanistan Mobile Roshan

Afghanistan Mobile Salam

<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>
[TD="width: 267"]Afghanistan[/TD]
[TD="width: 88, align: right"]93[/TD]
[TD="width: 66, align: right"]0.1575[/TD]

[TD="align: right"]9320[/TD]
[TD="align: right"]0.1575[/TD]

[TD="align: right"]9325[/TD]
[TD="align: right"]0.16[/TD]

[TD="align: right"]937[/TD]
[TD="align: right"]0.11172[/TD]

[TD="align: right"]9375[/TD]
[TD="align: right"]0.15382[/TD]

[TD="align: right"]937500[/TD]
[TD="align: right"]0.15382[/TD]

[TD="align: right"]9370[/TD]
[TD="align: right"]0.13472[/TD]

[TD="align: right"]9371[/TD]
[TD="align: right"]0.13472[/TD]

[TD="align: right"]93711[/TD]
[TD="align: right"]0.13472[/TD]

[TD="align: right"]9378[/TD]
[TD="align: right"]0.11172[/TD]

[TD="align: right"]9376[/TD]
[TD="align: right"]0.11413[/TD]

[TD="align: right"]9377[/TD]
[TD="align: right"]0.11413[/TD]

[TD="align: right"]9372[/TD]
[TD="align: right"]0.10238[/TD]

[TD="align: right"]9379[/TD]
[TD="align: right"]0.10238[/TD]

[TD="align: right"]9374[/TD]
[TD="align: right"]0.13692[/TD]

</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