Macro to perform a 'conditional replace'

[mojo707]

Hi, I have a worksheet where I need to do what I would describe as a 'conditional replace'.
For example I have two columns A and B. If the value in a given row in column A is the same as the value in the corresponding row in column B then I want to replace both values with the word "Match". Similarly, if the value for a given row in column A is not the same as the value in the corresponding row in column B (including the scenario that one cell is blank and the other is not blank) then I want to replace the value in column A with the text "P" and the corresponding value in column B with the text "Q".
I am doing this manually using the filters, entering the desired values manually and dragging down to replace but with 40,000 rows and lots of blanks this does take some time.
Could a macro automate this?

 

[mole999]

you can do things with a formula that won't destroy your original data in C =IF(A1=B1,"Match" etc. It does depend on if you need to double check you were correct etc

 

[Fluff]

How about

Sub mojo707()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Address & "=" & .Offset(, 1).Address & ",""Match"",""P"")")
      .Offset(, 1).Value = Evaluate("if(" & .Address & "=""Match"",""Match"",""Q"")")
   End With
End Sub

 

[mojo707]

How about

Sub mojo707()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Address & "=" & .Offset(, 1).Address & ",""Match"",""P"")")
      .Offset(, 1).Value = Evaluate("if(" & .Address & "=""Match"",""Match"",""Q"")")
   End With
End Sub

OK Thanks - Could the code above be modified to suit an excel table instead of referencing columns? I should have been more specific - where i said column A it is in fact a column titled "Compensation A" within an excel table and where i said column B it is a column titled "Compensation B"

 

[Fluff]

Very possibly, but I'm not sure how.

 

[Fluff]

Figured it out

Sub mojo707()
   Dim Add1 As String, Add2 As String
   
   With Range("table1").ListObject
      Add1 = .ListColumns("Compensation A").DataBodyRange.Address
      Add2 = .ListColumns("Compensation B").DataBodyRange.Address
   End With
   Range(Add1).Value = Evaluate("if(" & Add1 & "=" & Add2 & ",""Match"",""P"")")
   Range(Add2).Value = Evaluate("if(" & Add1 & "=""Match"",""Match"",""Q"")")
End Sub

 

[mojo707]

Thanks mole999 - I did consider that but was looking for a more automated solution as I may hand this work over eventually.

 

[mojo707]

Figured it out

Sub mojo707()
   Dim Add1 As String, Add2 As String
   
   With Range("table1").ListObject
      Add1 = .ListColumns("Compensation A").DataBodyRange.Address
      Add2 = .ListColumns("Compensation B").DataBodyRange.Address
   End With
   Range(Add1).Value = Evaluate("if(" & Add1 & "=" & Add2 & ",""Match"",""P"")")
   Range(Add2).Value = Evaluate("if(" & Add1 & "=""Match"",""Match"",""Q"")")
End Sub

Thanks Fluff - this worked well! appreciate you looking into it.

 

[Fluff]

You're welcome & thanks for the feedback