Campring sheets

[cornepiek]

hi all

i need some help comparing two worksheets.

the first worksheet contains a list of dialed telephone numbers.

the second list contains a list of destination prefixes and the destination description.

i need to cross reference the first 3 digits of the dialed number to the destination list, and then copy the destination description next to the dialed number.

here is an example on the call list
27231283054
27224667141
26445849705
27321487351
26467263726
27277263726
27277263726
27255957100
27218531898
27347318821
27445849705
27340568171
26645849705
27497268506


and the destination list
272 SAWC
273 SAKZN
274 SAEC
264 NAM
266 LES


and here is what i hope to achieve

27231283054 SAWC
27224667141 SAWC
26445849705 NAM
27321487351 SAKZN
26467263726 NAM
27277263726 SAWC
27277263726 SAWC
27255957100 SAWC
27218531898 SAWC
27347318821 SAKZN
27445849705 SAEC
27340568171 SAKZN
26645849705 LES
27497268506 SAEC



in some cases i will need to change the prefix length to 4 or 5 digits for more detailed descriptions.
also, both the calls list and the destination list will vary in length every time i need to run the report.

the prefix will always be from the left

please help

 

[Jonmo1]

If you can sort your Destination list ASCENDING by the prefix column, then you can use

=LOOKUP(LEFT(A2,3)+0,$E$2:$F$6)

A2 = Phone #
E2:F6 = Destination List sorted ascending by column E


Hope this helps.

Excel Workbook
	A	B	C	D	E	F
1	Phone #	Dest			Prefix	Dest
2	27231283054	SAWC			264	NAM
3	27224667141	SAWC			266	LES
4	26445849705	NAM			272	SAWC
5	27321487351	SAKZN			273	SAKZN
6	26467263726	NAM			274	SAEC
7	27277263726	SAWC
8	27277263726	SAWC
9	27255957100	SAWC
10	27218531898	SAWC
11	27347318821	SAKZN
12	27445849705	SAEC
13	27340568171	SAKZN
14	26645849705	LES
15	27497268506	SAEC
Sheet1

 

[Jonmo1]

OK, just got the last bit of your post

in some cases i will need to change the prefix length to 4 or 5 digits for more detailed descriptions.

Can you describe the situations where the prifix will be 4 or 5 digits?
We should be able to accomodate this...

 

[p45cal]

This will get you started; first make sure that everything in the two ranges is formatted as Text. Then enter the formula into B1, paying attention to the note under Formula Array: below, then copy down the formula as far as you need. Substitute range for the actual destination list range of course.

Excel Workbook
	A	B
1	27231283054	SAWC
2	27224667141	SAWC
3	26445849705	NAM
4	27321487351	SAKZN
5	26467263726	NAM
6	27277263726	SAWC
7	27277263726	SAWC
8	27255957100	SAWC
9	27218531898	SAWC
10	27347318821	SAKZN
11	27445849705	SAEC
12	27340568171	SAKZN
13	26645849705	LES
14	27497268506	SAEC
15
16
17	and the destination list
18	272	SAWC
19	273	SAKZN
20	274	SAEC
21	264	NAM
22	266	LES
Sheet

edit post posting:
seeing jonmo1's technique, I've adjusted my formula to not have to worry about how the data is formatted in either list (I think):
=VLOOKUP(LEFT(A1,3) & "",$A$18:$B$22 & "",2,FALSE)
also array-entered as before.

 

[cornepiek]

for the prefixes i currently have 3 digit prefixes.

in some cases i will need to split the 272 prefix into 2721 - 2729
thus making it a four digit prefix.
but from you formula i see that i can simply change the prefix length to accommodate for the longer prefixes.


is there a way that i can simplify copying the formula to the cells below?

usually i need to work with about 10- 15 000 call records.

in the formula, only the values a1, a2 ,a3 and such change.
how do i get around this

 

[p45cal]

Dealing with prefixes of different lengths, same array-entering and copying down as before, (sorting not required):

Excel Workbook
	A	B
1	27231283054	SAWC 3
2	27224667141	SAWC 24
3	26445849705	NAM
4	27321487351	SAKZN
5	26467263726	NAM
6	27277263726	SAWC
7	27277263726	SAWC
8	27255957100	SAWC
9	27218531898	SAWC
10	27347318821	SAKZN
11	27445849705	SAEC
12	27340568171	SAKZN
13	26645849705	LES
14	27497268506	SAEC
15
16
17	and the destination list
18	272	SAWC
19	273	SAKZN
20	274	SAEC
21	264	NAM
22	266	LES
23	2723	SAWC 3
24	27224	SAWC 24
Sheet

 

[Jonmo1]

You can just drag the formula down as is, the A2 will incriment as required.

Now, for the varying length of the prfix...
Will the rest of the number remain the same number of characters?
If so, then use

=LOOKUP(LEFT(A2,LEN(A2)-8)+0,$E$2:$F$6)

 

[cornepiek]

no, the number lenght is not fixed.

my entire range that i will work with includes local, national, and international numbers, so my number lengths can vary anything from 10 digits to 16 digits.

for instance all national numbers are 11 digits, so they are defined by the first 3 or 4 digits.
ie: 27212002000 is a local number where the prefix will be 272
27833003000 is a cell number, and the prefix will be 2783

for international numbers, the number length can vary from 10 -16 digits and the prefix varies from 1 to 8 digits..

there might also be a case where a number has a general description and a more detailed one

ie:
27833003000 can fall under the general SA Mobile destination 278
but can then be defined as 2783 for SA Mobile MTN.
in this case the rule must first look at the "lower" destination entry 278,
and then override is another rule exists with a more detailed description.

in most cases the destination sets are grouped by country, so all the detinations starting with 27 will be grouped for SA. they will however be grouped in rising order, so 27 will be first, then 278 later down the list, and 2783 will then follow that.
we will always keep to this format