Can we compare the Excel columns alone

[gopi09_m]

Can we compare the Excel columns alone ,not sheet to sheet or excel to excel.Its columns from two different excel files.

[GTO]

Eh?

If the column is being compared 'alone', what are we comparing to what?

Mark

[gopi09_m]

I want to compare two columns and those two columns belongs to two different excel files

[GTO]

Hi again,

Okay, let's say we look at cell A1 in Sheet1 of Book1.xls and compare this to cell A1 in Sheet1 of Book2.xls.

Are we looking to see if they match, don't match, one is greater than the other, or...?

Now regardless of our test, but let's just say we were testing for non-matches. If the values are not the same, then what are we doing?

Mark

[gopi09_m]

I want to compare the contents of both the cells whether they are equal or not .They may contain strings or integers or dat format or currency.And the result should be anything.just a messagebox will also be sufficient.

[GTO]

Create two workbooks, save as File1.xls and File2.xls.

In Sheet1 Column A of both, enter some matching and non-matching data for 10 or 20 rows.

In File1.xls, in a Standard Module:

Option Explicit

Sub exa()
Dim wbFile2 As Workbook
Dim rngThisWorkbook As Range, rngOtherWorkbook As Range
Dim lLastRowThisWB As Long, lLastRowOtherWB As Long, lLastRow As Long, i As Long

On Error Resume Next
Set wbFile2 = Workbooks("File2.xls")
On Error GoTo 0

If wbFile2 Is Nothing Then
MsgBox "File2.xls is not open", 0, vbNullString
Exit Sub
End If

lLastRowThisWB = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lLastRowOtherWB = wbFile2.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lLastRow = Application.Max(lLastRowOtherWB, lLastRowThisWB)

Set rngOtherWorkbook = wbFile2.Worksheets("Sheet1").Range("A1:A" & lLastRow)
Set rngThisWorkbook = ThisWorkbook.Worksheets("Sheet1").Range("A1:A" & lLastRow)
rngThisWorkbook.Offset(, 1).Value = _
Evaluate("=IF(" & rngThisWorkbook.Address(False, False) & _
"=[" & rngOtherWorkbook.Parent.Parent.Name & "]" & _
rngOtherWorkbook.Parent.Name & "!" & _
rngOtherWorkbook.Address(False, False) & ","""",""NO MATCH"")")
End Sub


This is just one way of course, you could loop thru checking the sister cell, or use .FormulaArray and overwrite the results with the vals.

Hope that helps,

Mark