matching country codes

ayalaz · Nov 14, 2005

I have a list of calls made to many countries around the world and a list of the country codes ranging from 1-5 digits. I need a way to match the country code part of the telephone number to the country code list and use the rate next to this code for calculating the cost!

Number Dialed Country Code Rate
[011359604503867 35 3
[0113545600938] 994 4
[011994505637853] 995 3
[01148225171582] 996 7
[011302310795526] 48 4
[01159889376468] 482 6
[01148507026968] 30 2
[01148225171582] 7 5
[0113904378558] 7095 6
[01170957979786] 33 1
[011963112779779] 85 3
[01133475488927]
[01185223441330]
[01131317681040]
[01170957979785]
[01196899314110]
[01170957979785]
[011968702772]
[01179168533650]
[0112022663499]
[011963112779779]
[01170957979785]

[/img]

ayalaz · Nov 14, 2005

To clarify I am attaching my data below

Oztiryakiler_05_Rapor.xls

A

B

C

D

E

F

1

NumberDialed

Cost

CountryCodes

Rate

2

[01135960450386]

35

3

[0113545600938]

994

4

[011994505637853]

995

3

5

[01148225171582]

996

7

6

[011302310795526]

48

4

7

[01159889376468]

482

6

8

[01148507026968]

30

2

9

[01148225171582]

7

5

10

[0113904378558]

7095

6

11

[01170957979786]

33

1

12

[011963112779779]

85

3

13

[01133475488927]

14

[01185223441330]

15

[01131317681040]

16

[01170957979785]

17

[01196899314110]

18

[01170957979785]

19

[011968702772]

20

[01179168533650]

21

[0112022663499]

22

[011963112779779]

23

[01170957979785]

Sheet1

plettieri · Nov 14, 2005

Hi:

On line 3 of your sample data you indicate the country code is '994', however, there is no '994' in the telephone number dialed on line 3.....is your listing possible one row offset, as there is a '994' on line 4 in the column telephone number 011994505637853 ?

plettieri

goblin · Nov 14, 2005

Your list of countries isn't correct. The second number there is Icelandic. The country-code for Iceland is 354, not 35.

The numbering scheme is quite horrible to comprehend and I haven't seen a simple number parser yet. See the following for an incomplete listing of countries and their numbering scheme.
http://en.wikipedia.org/wiki/Area_code

You can also find a complete listing at itu.org but they charge for the info.

You can get a complete listing in Icelandic (English in parentheses) at:
http://siminn.is/control/index?pid=57772

Good luck. Look forward to following this thread and the solutions you guys dream up

barry houdini · Nov 14, 2005

The biggest problem is that where you have, for instance, in row 5

[01148225171582]

from what you say you have no way of knowing whether the country code is 48 or 482, both of which are on your list

7 and 7095 would fall into the same category.

This solution will always assume the longer code if there is more than one possibility

for row 2

=INDEX($F$2:$F$12,LOOKUP(9.99999999999999E+307,MATCH(MID(A2,5,{1,2,3,4,5})+0,$E$2:$E$12,0)))

copy down for other rows

PS presupposes that your list of codes is correct :wink:

NBVC · Nov 14, 2005

My solution is a little more brute force, but I think it does what you need.

The Formula in E2 extracts the code (you probably don't need this, but it shows that the correct code was pulled):
=IF(COUNTIF($B$2:$B$12,MID(A2,5,5)),VLOOKUP(VALUE(MID(A2,5,5)),$B$2:$C$12,1,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,4)),VLOOKUP(VALUE(MID(A2,5,4)),$B$2:$C$12,1,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,3)),VLOOKUP(VALUE(MID(A2,5,3)),$B$2:$C$12,1,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,2)),VLOOKUP(VALUE(MID(A2,5,2)),$B$2:$C$12,1,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,1)),VLOOKUP(VALUE(MID(A2,5,1)),$B$2:$C$12,1,0),"")))))

The Formula in F2 extracts the cost (you can then multiply by minutes or whatever manipulation you need to do in another column):

=IF(COUNTIF($B$2:$B$12,MID(A2,5,5)),VLOOKUP(VALUE(MID(A2,5,5)),$B$2:$C$12,2,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,4)),VLOOKUP(VALUE(MID(A2,5,4)),$B$2:$C$12,2,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,3)),VLOOKUP(VALUE(MID(A2,5,3)),$B$2:$C$12,2,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,2)),VLOOKUP(VALUE(MID(A2,5,2)),$B$2:$C$12,2,0),IF(COUNTIF($B$2:$B$12,MID(A2,5,1)),VLOOKUP(VALUE(MID(A2,5,1)),$B$2:$C$12,2,0),"")))))

[HTML example removed]

Edited by Von Pookie

ayalaz · Nov 14, 2005

I should have provided the actual data earlier, but here it is anyway, the first sheet are the calls made, I need the formula to calculate the cost for each call. Second sheet below is a partial list of the Country codes, unfortunatley I cannot copy the full list here.

Houdini,
I tried your formula and it works for most of the calls

but fails for some, could it be because there are some calls starting with 1 instead of 011? meaning calls made to US.
-Al

CountryCodes.xls

F

G

H

I

2

Destination

Cost

Duration

Stat

3

[01135960450386]

0.871744507