Re: If Data Exists in Column A and in Column B Make List in Column C w/out Blanks/Err
not formula, but here's a macro:
Code
Sub CheckBothCols()
Dim col1 As New Collection
Dim col2 As New Collection
Dim sWord As String
For c = 0 To 1
If c = 0 Then
On Error Resume Next
Else
On Error GoTo skipIt:
End If
Range("A1").Offset(0, c).Select
While ActiveCell.Value <> ""
sWord = ActiveCell.Value
If c = 0 Then 'load the 1st col
col1.Add sWord, sWord
Else 'save only matching items from col2
If col1(sWord) = sWord Then col2.Add sWord, sWord
End If
skipIt:
ActiveCell.Offset(1, 0).Select 'next row
Wend
Next
'print out the list
Range("C1").Select
For c = 1 To col2.Count
ActiveCell.Value = col2(c)
ActiveCell.Offset(1, 0).Select 'next row
Next
Set col1 = Nothing
Set col2 = Nothing
End Sub
Display More