Need to Compare Values in Multiple Columns

[gspreeman]

I need to compare values in Column B to values in Column P to see if they are the same (note, these are two different sets of data so, the identical values we are searching for might be in B12 and P55. If we find the same value in B and P then we need to compare the value contained on the same row to B, the value for C and then on the same row as P, the value for Q. If C and Q DO NOT MATCH, in column R I would like to put the words, "Wrong Amt."

Thanks in advance.

[sktneer]

Try this code to see if this works as per your requirement.

Please Login or Register  to view this content.

See the attached sheet.

[jphart99]

sktneer,

I have a very similar situation and used your code to much success.

I have a follow-up question: How would I go about getting this code to generate a report of variances on the next sheet of the workbook? This report should include the values from columns B, C, P and Q.

Also, I made a few minor changes to your code:

Sub comparevalues()
Columns("R:S").Delete
Dim rng, rng1, cell As Range
Dim lr As Long
Dim n As Integer
lr = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("B2:B" & lr)
Set rng1 = Range("P2:P" & lr)
Application.ScreenUpdating = False
For Each cell In rng
On Error Resume Next
n = WorksheetFunction.Match(cell, rng1, 0)
If Err = 0 Then
If cell.Offset(0, 1) <> Cells(n + 1, "Q") Then
Cells(n + 1, "R") = "Wrong Amount"
Cells(n + 1, "S") = cell.Offset(0, 1)
End If
End If
Err = 0
Next cell
Columns("R:S").AutoFit
Application.ScreenUpdating = True
End Sub

Thanks,

jphart99

[sktneer]

Find the attached sheet to see if this works as per your requirement. The columns of interest will be copied to Sheet2.

[jphart99]

Sktneer,

Thanks very much for your help! This has worked very well!

[sktneer]

You're welcome.
If that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.