OzGrid

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

Category: [Excel]  Demo Available

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.