How to compare 2 excel sheet column values

Question

I have 2 Excel sheets (using Excel 2007)

Excel sheet1 with column names

name  
kumar 
manu  
kiran  
anu   

Excel sheet2 with column names

name 
kumar   
anu 

I will upload the sheets and then I click on a button (here I will compare the names columns from each sheet) then I need to add the names missing from sheet 2 to another Excel sheet and save to D:\names.xlsx

So names.xlsx sheet should contain

names
manu  
kiran

Hope my question is clear, any help would be great yaar

Answer 1

The easiest way to do this would be to:

1. read the excel file into a datatable in C#
2. use the Merge feature of datatables to merge the columns
3. write the data back to the excel file.

Answer 2

You can use ADO.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used. 
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open strCon

strSQL = "SELECT [Name] " _
       & "FROM [Sheet1$] a " _
       & "LEFT JOIN [Sheet2$] b " _
       & "ON a.[Name]=b.[Name] " _
       & "WHERE b.Name Is Null"

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs=Nothing
cn.Close
Set cn=Nothing