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>

 

[petertenthije]

Assuming the data is stored in colums A through C, place this formula in row 2 of column D.

=IFERROR(VLOOKUP((NUMBERVALUE(LEFT($B2,5)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP((NUMBERVALUE(LEFT($B2,4)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP((NUMBERVALUE(LEFT($B2,3)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP((NUMBERVALUE(LEFT($B2,2)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP((NUMBERVALUE(LEFT($B2,1)),$B$1:$C1,2,FALSE)=C2,)))))

Assumptions made, there are no prefix codes longer then 6 digits.
The list is sorted as per your example (short to long and low to high)

The output will be either TRUE or FALSE. The TRUEs can be deleted.

The same goes for 937500 should be deleten beceause if that prefix is not in the list the rate of the prefix 9375 will be charged.

Fixed that for you.

 

[Yaseraliakram]

hi

thanks for the reply

i think there is a problem with your formula, maybe not coppied good can you send again ?

 

[petertenthije]

hi

thanks for the reply

i think there is a problem with your formula, maybe not coppied good can you send again ?

=iferror(vlookup((numbervalue(left($b2,5)),$b$1:$c1,2,false)=c2,iferror(vlookup((numbervalue(left($b2,4)),$b$1:$c1,2,false)=c2,iferror(vlookup((numbervalue(left($b2,3)),$b$1:$c1,2,false)=c2,iferror(vlookup((numbervalue(left($b2,2)),$b$1:$c1,2,false)=c2,iferror(vlookup((numbervalue(left($b2,1)),$b$1:$c1,2,false)=c2,)))))

 

[Yaseraliakram]

there is still a problem with your formula mate...
the formula is not accepting, i am missing something

 

[petertenthije]

there is still a problem with your formula mate...
the formula is not accepting, i am missing something

From my first reply, you have to paste all five rows in the same cell.

Does your version of excel use comma to break up formulas, or something else?
On this board typically commas are used, for instance LEFT($B2,5), but there are also versions of Excel that use semicolons LEFT($B2;5). Otherwise you might need to adjust the commas.

 

[Fluff]

@petertenthije
You have too many brackets in your formula, there is an extra bracket here
vlookup((numbervalue
for each lookup

 

[petertenthije]

@petertenthije
You have too many brackets in your formula, there is an extra bracket here
vlookup((numbervalue
for each lookup

Thanks! That probably happened when I translated the formula from Dutch VERT.ZOEKEN to English VLOOKUP.

Type all five rows into cell D2, and copy down.

=IFERROR(VLOOKUP(NUMBERVALUE(LEFT($B2,5)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP(NUMBERVALUE(LEFT($B2,4)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP(NUMBERVALUE(LEFT($B2,3)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP(NUMBERVALUE(LEFT($B2,2)),$B$1:$C1,2,FALSE)=C2,
IFERROR(VLOOKUP(NUMBERVALUE(LEFT($B2,1)),$B$1:$C1,2,FALSE)=C2,)))))

 

[Yaseraliakram]

yes, found the problem, it took me a while but i managed,
just a quick question further.

What if there are prefixes up to 12 digits ?
Can i use the same formula ?

 

[petertenthije]

yes, found the problem, it took me a while but i managed,
just a quick question further.

What if there are prefixes up to 12 digits ?
Can i use the same formula ?

Sure, just add a copy of the first row, and modify the (LEFT($B2,5)) bit by increasing the 5 by the number of digits you want to review.