How to find/return first nonblank value in adjacent cell from column with duplicate values
Category: [Excel] Demo Available
How to find/return first nonblank value in adjacent cell from column with duplicate values
Requirement:
The user has attached a simple workbook with a dummy table of values. What the user needs is a macro that will loop through all the unique values in column A of the "Merge" tab and do a "vlookup" of sorts to column A on the "Master" tab where there will be duplicates of that value.
When a match is found, if the phone or email column is populated, it should return the first value it finds for either one to the corresponding column B and C on the "Merge" tab. If either the phone or email column is blank on the "Master" tab, it should continue looking down the list of values until both the phone and email are populated or until it runs out of column A matches to evaluate. It should then continue with the next name on the Merge tab until all unique names have been evaluated.
Solution:
Click the button on Merge sheet.
Code assigned to the button is:
Code:
Sub GetPhoneAndEmail()
Dim x, y, i As Long, ii As Long, iii As Long
y = Sheets("Master").Cells(1).CurrentRegion
Application.ScreenUpdating = 0
With Sheets("Merge").Cells(1).CurrentRegion
x = .Value
For i = 2 To UBound(x, 1)
For ii = 2 To UBound(y, 1)
If LCase(y(ii, 1)) = LCase(x(i, 1)) Then
If y(ii, 2) <> "" Then x(i, 2) = y(ii, 2)
If y(ii, 3) <> "" Then x(i, 3) = y(ii, 3)
If x(i, 2) <> "" And x(i, 3) <> "" Then Exit For
End If
Next
Next
.Value = x
.Columns.AutoFit
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 copy cell data to another sheet and save it automatically |
| How to use cell content as input to a structured reference as part of a lookup function |
| How to use a Macro to copy rows from multiple worksheets based on a cell value greater than zero |
| How to COUNTIF using input cell as range depth |
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.