Macro to compare columns and output the results

[Beth]

Hi,

I have another question.

I'm comparing two pairs of columns (Column D with E, Column F with G. The
data in these are vlookup-ed from other sheets) and putting down the
comparison results in Column H.

If Column D & E matches, Column F & G matches => Column H results: "ID
matches, number of files matches"
If Column D & E matches, Column F & G does NOT match => Column H results:
"ID matches, number of files does NOT match"
If Column D & E does NOT match, Column F & G match => Column H results: "ID
does NOT match, number of files matches"
If Column D & E does NOT match, Column F & G does NOT match => Column H
results: "ID does NOT match, number of files does NOT match"

If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute
with "ID contains #N/A" in the results in Column H. Same for Column F & G, if
#N/A is present, substitute "Number of files does not match" with "Number of
files contains #N/A". So in Column H there will also be combinations like "ID
matches, number of files contains #N/A", "ID contains #N/A, number of files
does NOT match", and so on.

Using EXACT with much filtering/sorting can do the job, but it would be
great if this can be done with a macro

TIA

Beth

 

[Bernie Deitrick]

Beth,

Sub HiLiteMatches()
Dim myR As Long

For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row
If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value)
Then
Range("H" & myR).Value = "ID contains #N/A, "
Else
If Range("D" & myR).Value = Range("E" & myR).Value Then _
Range("H" & myR).Value = "ID matches, "
If Range("D" & myR).Value <> Range("E" & myR).Value Then _
Range("H" & myR).Value = "ID does NOT match, "
End If
If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value)
Then
Range("H" & myR).Value = Range("H" & myR).Value & _
"number of files contains #N/A, "
Else
If Range("F" & myR).Value = Range("G" & myR).Value Then _
Range("H" & myR).Value = Range("H" & myR).Value & _
"number of files matches, "
If Range("F" & myR).Value <> Range("G" & myR).Value Then _
Range("H" & myR).Value = Range("H" & myR).Value & _
"number of files does NOT match, "
End If

Next myR

End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry. The lines

If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value)
Then

and

If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value)
Then

both wrapped incorrectly - the Then should go on the same line as the first part...


HTH,
Bernie
MS Excel MVP

 

[Beth]

Hi Bernie,

Thanks! I figured out the wrapping and it worked like a charm

I modified the macro to let it auto insert a new column for H
(.Columns("H").Insert). But how do I make it add the column title in H1 ?

TIA

Beth

 

[Bernie Deitrick]

Beth,

Great.

Immediately after your .Columns("H").Insert, use code like

Range("H1").Value = "Column Title for H"

HTH,
Bernie
MS Excel MVP

 

[Beth]

Bernie,

Thanks, this works.

Beth

Beth,

Great.

Immediately after your .Columns("H").Insert, use code like

Range("H1").Value = "Column Title for H"

HTH,
Bernie
MS Excel MVP