Excel compare two columns and highlight duplicates

Question

I have an excel file with 10 000 rows in column A some values are the same   Example   A1 - P7767  A2 - P3443  A3 - P7767  A4 - P8746  A5 - P9435   etc     I then have another column with 100 rows which have some of the values found in column A   B1 - P7767  B2 - P8746   etc     I need to highlight all cells in column A where the value is found in any of the values in column B  So basically column B checks to see if it can find the same value anywhere in column A  if true highlight the cell leaving any cells white when the value is not found in column B  I hope I have explained this well  I have done some research and I believe I need to use conditional formatting to get this result but I am really stuck on the formula to use and cannot seem to find an example online  Maybe I am not searching the correct term as I m not sure on what this is exactly called

User · Answer

I was trying to compare A-B columns and highlight equal text, but usinng the obove fomrulas some text did not match at all. So I used form (VBA macro to compare two columns and color highlight cell differences) codes and I modified few things to adapt it to my application and find any desired column (just by clicking it). In my case, I use large and different numbers of rows on each column. Hope this helps:

Sub ABTextCompare()

Dim Report As Worksheet
Dim i, j, colNum, vMatch As Integer
Dim lastRowA, lastRowB, lastRow, lastColumn As Integer
Dim ColumnUsage As String
Dim colA, colB, colC As String
Dim A, B, C As Variant

Set Report = Excel.ActiveSheet
vMatch = 1

'Select A and B Columns to compare
On Error Resume Next
 Set A = Application.InputBox(Prompt:="Select column to compare", Title:="Column A", Type:=8)
  If A Is Nothing Then Exit Sub
colA = Split(A(1).Address(1, 0), "$")(0)
 Set B = Application.InputBox(Prompt:="Select column being searched", Title:="Column B", Type:=8)
   If A Is Nothing Then Exit Sub
  colB = Split(B(1).Address(1, 0), "$")(0)
 'Select Column to show results
 Set C = Application.InputBox("Select column  to show results", "Results", Type:=8)
    If C Is Nothing Then Exit Sub
  colC = Split(C(1).Address(1, 0), "$")(0)

'Get Last Row
lastRowA = Report.Cells.Find("", Range(colA & 1), xlFormulas, xlByRows, xlPrevious).Row - 1 ' Last row in column A
lastRowB = Report.Cells.Find("", Range(colB & 1), xlFormulas, xlByRows, xlPrevious).Row - 1 ' Last row in column B

 Application.ScreenUpdating = False
'***************************************************
For i = 2 To lastRowA
      For j = 2 To lastRowB
          If Report.Cells(i, A.Column).Value <> "" Then
              If InStr(1, Report.Cells(j, B.Column).Value, Report.Cells(i, A.Column).Value, vbTextCompare) > 0 Then
                  vMatch = vMatch + 1
                  Report.Cells(i, A.Column).Interior.ColorIndex = 35 'Light green background
                  Range(colC & 1).Value = "Items Found"
                  Report.Cells(i, A.Column).Copy Destination:=Range(colC & vMatch)
                  Exit For
              Else
                  'Do Nothing
              End If
          End If
      Next j
  Next i
If vMatch = 1 Then
    MsgBox Prompt:="No Itmes Found", Buttons:=vbInformation
End If
'***************************************************
Application.ScreenUpdating = True

End Sub

User · Answer

The easiest way to do it  at least for me  is   Conditional format-  Add new rule- Set your own formula     ISNA MATCH A2  B  B 0     Where A2 is the first element in column A to be compared and B is the column where A s element will be searched   Once you have set the formula and picked the format  apply this rule to all elements in the column   Hope this helps

User · Answer

NOTE  You may want to remove duplicate items  eg duplicate entries in the same column  before doing these steps to prevent false positives   Select both columns click Conditional Formatting click Highlight Cells Rules click Duplicate Values  the defaults should be OK  Duplicates are now highlighted in red

User · Answer

Suppose you want to compare a column A and column H in a same spreadsheet    You need to go another column next to these 2 columns and paste this formula         Sheet1 A A Sheet1 H H    this will display  FALSE or TRUE in the column   So you can use this new column to color the non matching values using conditional color formatting feature

User · Answer

A1 --  conditional formatting --  cell value is B1 --  format  whatever you want  hope that helps

User · Answer

There may be a simpler option  but you can use VLOOKUP to check if a value appears in a list  and VLOOKUP is a powerful formula to get to grips with anyway    So for A1  you can set a conditional format using the following formula    NOT ISNA VLOOKUP A1  B  B 1 FALSE      Copy and Paste Special   Formats to copy that conditional format to the other cells in column A   What the above formula is doing    VLOOKUP is looking up the value of Cell A1  first parameter  against the whole of column B   B  B   in the first column  that s the 3rd parameter  redundant here  but typically VLOOKUP looks up a table rather than a column   The last parameter  FALSE  specifies that the match must be exact rather than just the closest match  VLOOKUP will return  ISNA if no match is found  so the NOT ISNA       returns true for all cells which have a match in column B

User · Answer

Don t wana do soo much work guyss    Just Press Ctr and select Colum one and Press Ctr and select colum two  Then click conditional formatting -  Highlight Cell Rules -  Equel To   and thats it  your done

User · Answer

A simple formula to use is   COUNTIF  B  B A1    Formula specified is for cell A1   Simply copy and paste special - format to the whole of column A

[excel] Excel compare two columns and highlight duplicates

Examples related to excel

Examples related to excel-formula