How to Sorting by phone #, phone # in different columns, phone # with extentions

How to sort by phone #, phone # in different columns, phone # with extentions

Requirement:

The companies can be identified by various fields but the focus is on the three different phone number fields (mobile, other, office).

Some contacts work for multiple companies, some work for multiple locations of one company (each with separate accounts), some companies share phone numbers exactly or with extensions tacked on. The goal is to find out what numbers go to what company and or contact by using either the exact value or the value with an extension (extra digits or labled "ext").

The user is trying to search through the three phone number columns for a number from a call sheet, and then every time find that number exactly or with an extension; the user wants to copy the whole row found in and put it on another sheet with the searched for number.

Solution:

Code:

Option Explicit

Sub ExtractData()
    Dim x, y, z(), e, i As Long, ii As Long, iii As Long, iv As Long
    
    x = Sheets("Contacts").Cells(1).CurrentRegion
    With Sheets("Lookup Numbers").Cells(1).CurrentRegion
        y = .Offset(1).Resize(.Rows.Count - 1)
    End With
    For Each e In y
        For i = 2 To UBound(x, 1)
            For ii = 8 To 10
                If Left(Trim(x(i, ii)), Len(Trim(e))) = Trim(e) Then
                    iii = iii + 1: ReDim Preserve z(1 To UBound(x, 2), 1 To iii)
                    For iv = 1 To UBound(z, 1)
                        z(iv, iii) = x(i, iv)
                    Next
                    Exit For
                End If
            Next
        Next
    Next
    With Sheets("Extracted Data")
        .Cells(1).CurrentRegion.Offset(1).Clear
        .[a2].Resize(UBound(z, 2), UBound(z, 1)) = Application.Transpose(z)
        .Columns.AutoFit
        .Activate
    End With
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.