Comparing Datasheets and movind data

[cornepiek]

hi all.

can someone assist.

i have to compare two datasheets.

the first sheet contains a list of telephone numbers. these can range from 10 to 18 digits.

the second sheet contains a list of country prefixes. these can range from 1 to 6 digits.

i need to match the dialed numbers from sheet one the specified destination prefixes on sheet two.
the prefixes will always match the numbers from the left.
ie
dialed number can be 27212002040
prefix will be 272

once a prefix match has been found, the destination must be added to the dialed number sheet

the matching has to check for 1 digit prefixes, then 2 digit, then 3 digit, etc, and override the previous match if it exists.

we may have multiple prefixes for the same number, but with more digits
ie: 27212002040 will match prefix 27 as well as prefix 272. we desire the more detail (longer) prefix match

Sheet 1
A1 - Number Dialed
A2 -27212002040
A3 - 27726211098
A4 - 442078012300
A5 - 27105905577

Sheet 2
A1 - Prefix B1 - Destination
A2 - 27 B2 - South Africa
A3 - 272 B3 - South Africa WC
A4 - 277 B4 - South Africa Mobile
A5 - 44 B5 - UK General



this is what we hope to achive:
A1 - Number Dialed B1 - Destination
A2 -27212002040 B2 - South Africa WC
A3 - 27726211098 B3 - South Africa Mobile
A4 - 442078012300 B4 - UK General
A5 - 27105905577 B5 - South Africa


as you can see B2 & B3 matched on 3 digits, but would also have matched on 2 digits.


can someone please help with this.
i have to manually match this at the moment and the dailed umber data sheets are in excess of 400000 records. as you can imagine, it takes quite a while.

thanks

 

[Comfy]

Something like this possibly?

Excel Workbook
	A	B	C	D	E	F	G
1	27212002040	South Africa WC			27	South Africa
2	27726211098	South Africa Mobile			272	South Africa WC
3	442078012300	UK General			277	South Africa Mobile
4	27105905577	South Africa			44	UK General
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=IF(ISNA(VLOOKUP(LEFT(A1,3)+0,$E$1:$F$4,2,FALSE)),VLOOKUP(LEFT(A1,2)+0,$E$1:$F$4,2,FALSE),VLOOKUP(LEFT(A1,3)+0,$E$1:$F$4,2,FALSE))
B2	=IF(ISNA(VLOOKUP(LEFT(A2,3)+0,$E$1:$F$4,2,FALSE)),VLOOKUP(LEFT(A2,2)+0,$E$1:$F$4,2,FALSE),VLOOKUP(LEFT(A2,3)+0,$E$1:$F$4,2,FALSE))
B3	=IF(ISNA(VLOOKUP(LEFT(A3,3)+0,$E$1:$F$4,2,FALSE)),VLOOKUP(LEFT(A3,2)+0,$E$1:$F$4,2,FALSE),VLOOKUP(LEFT(A3,3)+0,$E$1:$F$4,2,FALSE))
B4	=IF(ISNA(VLOOKUP(LEFT(A4,3)+0,$E$1:$F$4,2,FALSE)),VLOOKUP(LEFT(A4,2)+0,$E$1:$F$4,2,FALSE),VLOOKUP(LEFT(A4,3)+0,$E$1:$F$4,2,FALSE))

 

[Comfy]

Too late to edit.

Previous post fulfils the example but not the problem. Re: 6 digit prefix.

Back to the drawing board

 

[cornepiek]

your formula first checks for 3 digits, from left, the 2, the again 3.

what if we step it 6 times, first to match for 1, then 2, then 3 etc.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Jun57
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn1 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range, Dn2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oCk [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
With Sheets("sheet1") '[COLOR="Green"][B]sht1[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng1 = .Range(.Range("A2"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    With Sheets("sheet2") '[COLOR="Green"][B]sht2[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A2"), .Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng1
    [COLOR="Navy"]For[/COLOR] oCk = 1 To 3
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn2 [COLOR="Navy"]In[/COLOR] Rng2
            [COLOR="Navy"]If[/COLOR] Dn2 = Val(Left(Dn1, Len(Dn2))) [COLOR="Navy"]Then[/COLOR] Temp = Dn2.Offset(, 1)
        [COLOR="Navy"]Next[/COLOR] Dn2
    [COLOR="Navy"]Next[/COLOR] oCk
        Dn1.Offset(, 1) = Temp
[COLOR="Navy"]Next[/COLOR] Dn1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Comfy]

Came up with this:

Function Prefix(inumber As String, iIndex As Range, iMatch As Range) As String
    For i = 6 To 1 Step -1
        For j = UBound(iIndex.Value2) To 1 Step -1
            If Val(Left(inumber, i)) = Val(iMatch.Value2(j, 1)) Then Prefix = iIndex.Value2(j, 1): Exit Function
        Next j
    Next i
End Function

Excel Workbook
	A	B	C	D	E	F
1	27212002040	South Africa WC		27	South Africa
2	27726211098	South Africa Mobile		272	South Africa WC
3	442078012300	UK General		277	South Africa Mobile
4	27105905577	South Africa		442078	UK General
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=Prefix(A1,$E$1:$E$4,$D$1:$D$4)
B2	=Prefix(A2,$E$1:$E$4,$D$1:$D$4)
B3	=Prefix(A3,$E$1:$E$4,$D$1:$D$4)
B4	=Prefix(A4,$E$1:$E$4,$D$1:$D$4)