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

< Back to Search results

 Category: [Excel]  Demo Available 

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




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.




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)
                    Exit For
                End If
    With Sheets("Extracted Data")
        .[a2].Resize(UBound(z, 2), UBound(z, 1)) = Application.Transpose(z)
    End With
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by KjBox.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to look and sort blocks of rows
How to use VBA code to sort worksheets based on a pre-sorted named-range
How to sort results after copying data from multiple sheets


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)