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.
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.