compare two columns in excel

February 10, 2009 at 01:32:15
Specs: Windows XP
Hi,
I have got two columns in excel table. Both columns contain names of companies in Croatia. My task is to compare those columns to see if there are duplicate entries. If I find company"x" in column A and column B that entry should be moved from column A and pasted in column C so that at the end I have column A and column B that have no identical entries and column C that contain all duplicate entries found in A and B.
Help would be much appreciated.

See More: compare two columns in excel

Report •


#1
February 10, 2009 at 10:31:14
Assuming your lists start in A1 and B1, and you want your results to start in C1, this should work:

Sub CompCol()
CellCount = Range("A" & Rows.Count).End(xlUp).Row
 For NxtChk = CellCount To 1 Step -1
  With Columns("B")
   Set c = .Find(Cells(NxtChk, 1), lookat:=xlWhole)
    If Not c Is Nothing Then
     NxtCell = NxtCell + 1
      Cells(NxtChk, 1).Copy Destination:=Cells(NxtCell, 3)
      Cells(NxtChk, 1).Delete shift:=xlUp
    End If
  End With
 Next
End Sub


Report •

#2
February 11, 2009 at 06:48:42
This works perfectly!
Thank You very much.

Report •

#3
February 12, 2009 at 11:12:56
Glad I could help.

Thanks for the feedback.


Report •

Related Solutions

#4
February 26, 2009 at 09:32:18
I am trying to accomplish the same task, but the code won't work for me. I am running excel 2007, and i have entered the code as a macro and tried to run it, but nothing happened. Can you help?

Report •

#5
March 25, 2009 at 09:14:39
Hi.
Where is the code from Response 1 supposed to be inputted in Excel?

Report •

#6
March 26, 2009 at 06:09:28
Open the VBA editor, Insert a Module and paste the code in.

After that it should run against the currently active sheet.


Report •


Ask Question