Macro for comparing columns and highlighting matching/differences

[harpreet.noni]

Hi,

I am currently working on a simple requirement where I need to compare two columns (says Col C and F) in same worksheet with below requirements

1) If the value matches between two columns, the matching value is written to another column say D
2) For all matching, the rows/value (in column C and D) should be highlighted red and
3) For non-matching, the rows/values (in column C and D) should be highlighted green

More details.

The value in Column F is appended with timestamp (last 5 characters). Column C values are ok. While comparing, I want the Col F to ignore its last 5 characters and then compare values with Column C

Can anyone please assist here?

Work done till now.

This is what I could come up for now.

Sub compare_cols()

Dim c As Range
Dim d As Range

Set Report = Excel.Worksheets("Sheet1")
LRa = Range("C" & Rows.Count).End(xlUp).Row
LRb = Range("F" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To LRa
For Each c In Worksheets("Sheet1").Range("C2:C" & LRa)
For Each d In Worksheets("Sheet1").Range("F2:F" & LRb)
c.Interior.Color = vbRed
If (InStr(1, Left(d, Len(d) - 5), c, 1) > 0) Then
c.Interior.Color = vbGreen
Exit For
End If
Next
Next
Next i

Application.ScreenUpdating = True

End Sub

 

[Claus Busch]

Hi,

Am Tue, 22 Apr 2014 10:51:29 -0700 (PDT) schrieb
(e-mail address removed):

1) If the value matches between two columns, the matching value is written to another column say D
2) For all matching, the rows/value (in column C and D) should be highlighted red and
3) For non-matching, the rows/values (in column C and D) should be highlighted green

try:

Sub CompareCols()
Dim LRowC As Long, LRowF As Long
Dim rngCF As Range, rngCC As Range
Dim n As Long

Application.ScreenUpdating = False
With Sheets("Sheet1")
LRowC = .Cells(Rows.Count, "C").End(xlUp).Row
LRowF = .Cells(Rows.Count, "F").End(xlUp).Row
n = 2

For Each rngCC In .Range("C2:C" & LRowC)
For Each rngCF In .Range("F2:F" & LRowF)
If rngCC = Int(rngCF) Then
rngCC.Interior.Color = vbGreen
.Range("D" & n) = rngCC
n = n + 1
Exit For
Else
rngCC.Interior.Color = vbRed
End If
Next
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[harpreet.noni]

Hi, I am currently working on a simple requirement where I need to compare two columns (says Col C and F) in same worksheet with below requirements 1) If the value matches between two columns, the matching value is written to another column say D 2) For all matching, the rows/value (in column C and D) should be highlighted red and 3) For non-matching, the rows/values (incolumn C and D) should be highlighted green More details. The value in Column F is appended with timestamp (last 5 characters). Column C values are ok. While comparing, I want the Col F to ignore its last 5 characters and then compare values with Column C Can anyone please assist here? Work done till now. This is what I could come up for now. Sub compare_cols() Dim c As Range Dim d As Range Set Report = Excel.Worksheets("Sheet1") LRa = Range("C" & Rows.Count).End(xlUp).Row LRb = Range("F" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For i = 2 To LRa For Each c In Worksheets("Sheet1").Range("C2:C" & LRa) For Each d In Worksheets("Sheet1").Range("F2:F" & LRb) c.Interior.Color = vbRed If (InStr(1, Left(d, Len(d) - 5), c, 1) > 0) Then c.Interior.Color = vbGreen Exit For End If Next Next Next i Application.ScreenUpdating = True End Sub

 

[harpreet.noni]

Thanks Claus. This seems to be working fine.

Quick question, how can I change this code to also highlight values in column "D" (the column where matching values are written to)? Also, not matching value, how can we modify to include a literal say "not found".

Appreciate your response

Thanks

 

[Claus Busch]

Hi,

Am Tue, 22 Apr 2014 12:59:03 -0700 (PDT) schrieb
(e-mail address removed):

Quick question, how can I change this code to also highlight values in column "D" (the column where matching values are written to)? Also, not matching value, how can we modify to include a literal say "not found".

try:

Sub CompareCols()
Dim LRowC As Long, LRowF As Long
Dim rngCF As Range, rngCC As Range
Dim n As Long
Dim NoMatch As Boolean

Application.ScreenUpdating = False
With Sheets("Sheet1")
LRowC = .Cells(Rows.Count, "C").End(xlUp).Row
LRowF = .Cells(Rows.Count, "F").End(xlUp).Row
n = 2

For Each rngCC In .Range("C2:C" & LRowC)
For Each rngCF In .Range("F2:F" & LRowF)
If rngCC = Int(rngCF) Then
rngCC.Interior.Color = vbGreen
.Range("D" & n) = rngCC
.Range("D" & n).Interior.Color = vbGreen
n = n + 1
NoMatch = False
Exit For
Else
rngCC.Interior.Color = vbRed
NoMatch = True
End If
Next
If NoMatch = True Then
.Range("D" & n) = "not found"
n = n + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[harpreet.noni]

Perfect. Thanks Claus.

 

[harpreet.noni]

I had an situation here. For month end, we had around 11K filenames listed under column C and column F. Using above code, tok around 50 mins to complete processing.

Is there a way to better organize above code? Instead of each cell by cell comparison, can we someway use vlookup. Not sure though.

Thanks in advance

 

[GS]

I had an situation here. For month end, we had around 11K filenames

listed under column C and column F. Using above code, tok around 50
mins to complete processing.

Is there a way to better organize above code? Instead of each cell by
cell comparison, can we someway use vlookup. Not sure though.

Thanks in advance

If the range is loaded into an array it can be process in memory orders
of magnitude faster than accessing the sheet each time. I won't have
time to rewrite Claus' code for you but he may step in and do that
anyway!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi,

Am Fri, 2 May 2014 14:05:23 -0700 (PDT) schrieb (e-mail address removed):

I had an situation here. For month end, we had around 11K filenames listed under column C and column F. Using above code, tok around 50 mins to complete processing.

I guess Conditional Formatting will be the fastest method.
Try:

Sub CompareCols()
Dim LRowC As Long, LRowF As Long
Dim rngCF As Range

Application.ScreenUpdating = False

With ActiveSheet
LRowC = .Cells(Rows.Count, "C").End(xlUp).Row
LRowF = .Cells(Rows.Count, "F").End(xlUp).Row
Set rngCF = Range("F2:F" & LRowF)

.Range("D2" & LRowC).Formula = _
"=IF(SUMPRODUCT(N(INT($F$2:$F$" & LRowF & ")=$C2))>0,C2,""Not
Found"")"

.Range("C2" & LRowC).FormatConditions.Delete

.Range("C2" & LRowC).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2=$D2"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.Color = vbGreen
End With
End With

.Range("C2:C" & LRowC).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2<>$D2"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.Color = vbRed
End With
End With
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[GS]

Nice, Claus!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[harpreet.noni]

.Range("D2" & LRowC).Formula = _
"=IF(SUMPRODUCT(N(INT($F$2:$F$" & LRowF & ")=$C2))>0,C2,""Not Found"")"

The above line does not evaluate successfully. The column D gets populated with "#VALUE!" (A value used in the column is of wrong data type) error.

My both C and F values are like below. These are filenames with date valuesappended. BOth columns are of "General" data type.

thisisjust.atest.20140505

How can we correct this error. Please advise.

 

[Claus Busch]

Hi,

Am Mon, 5 May 2014 11:34:40 -0700 (PDT) schrieb (e-mail address removed):

My both C and F values are like below. These are filenames with date values appended. BOth columns are of "General" data type.

thisisjust.atest.20140505

please write some examples of values in C and F. What do you want to
compare? The whole string, the name or the date?


Regards
Claus B.

 

[harpreet.noni]

Thanks for the reply.

Format is something like this for columns C and F. DO is where the final values are being populated.

Column C Column F Column D
US0002.cc00_564547.w042914 US0001.cc00_45343.w042914 Not found
US0002.cc00_564547.w042914 US0002.cc00_564547.w043014 Not found
US0001.cc00_453434.w043014 US0002.cc00_564547.w050114 Not found
US0002.cc00_564547.w042914 US0001.cc00_453434.w050114 Not found
US0001.cc00_453434.w050114 US0002.cc00_564547.w050214 Found (Col C value is found in Column F)


Thanks

 

[Claus Busch]

Hi,

Am Mon, 5 May 2014 15:10:47 -0700 (PDT) schrieb (e-mail address removed):

Column C Column F Column D
US0002.cc00_564547.w042914 US0001.cc00_45343.w042914 Not found
US0002.cc00_564547.w042914 US0002.cc00_564547.w043014 Not found
US0001.cc00_453434.w043014 US0002.cc00_564547.w050114 Not found
US0002.cc00_564547.w042914 US0001.cc00_453434.w050114 Not found
US0001.cc00_453434.w050114 US0002.cc00_564547.w050214 Found (Col C value is found in Column F)

try:

Sub CompareCols()
Dim LRowC As Long, LRowF As Long

Application.ScreenUpdating = False

With ActiveSheet
LRowC = .Cells(Rows.Count, "C").End(xlUp).Row
LRowF = .Cells(Rows.Count, "F").End(xlUp).Row

.Range("C").FormatConditions.Delete
.Range("D220000").ClearContents

.Range("D2" & LRowC).Formula = _
"=IF(Countif($F$2:$F$" & LRowF & ",$C2)>0,C2,""Not Found"")"

.Range("C2" & LRowC).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2=$D2"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.Color = vbGreen
End With
End With

.Range("C2:C" & LRowC).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2<>$D2"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.Color = vbRed
End With
End With
End With
Application.ScreenUpdating = True

End Sub


Regards
Claus B.

 

[harpreet.noni]

Thanks. Due to some reason, this is still giving me hard time. For now, I am still using the old code.

I have another variation to the data being provided to me.

Col1 Col2 Col3 Col4 Status
File1 (e-mail address removed) Account1 (e-mail address removed)2 Present
File2 (e-mail address removed) Account2 (e-mail address removed)5 Present
File3 b@@ Account3 (e-mail address removed)1 Present
file3Account3 Present
file3Account56 N/A


What we are doing is concenating col1,col2 and col3 and comparing the values against values in col4.

1) If value is found, status says Present
2) If its not found, col1 and col3 are concanated and then compared againstcol4, if match found, then status column says present
3) else "N/A"