Solved: Compare columns in different excel files

[volkerpeter]

Dear Members , i am in urgent need of a excel vba code . I have a master file and a slave . Master is updated every 2-3 days . I have to compare a master with slave . The Problem is, I have table A B C in excel . I have to check that , the tuple A and B have a value has some value in table C. The value in C gets update frequently .

The puspose is , to find out , if the Tuple of A and B , has a new value in table C in the master file . I have to copmare which changes have taken place and then update them manually.

eg, a Product no. in table A is using a cable number in table B had a part number 0234ED which has updated to 0234DD. I have to find out if this product , with the same cable used before is now using a diffeernt or the same part number? .

I hope i have been able to explain and have not confused you .

How should I do this , please Help.

Regards Peter

[GTO]

Greetings Peter,

I think it would benefit us if you could zip an example of both master/slave, and show which is to be updated thru code. In other words, a 'Before' and 'After'.

[volkerpeter]

Thanks GTO . i dont have the original files at my PC , but i have created an example. The Forum is not letting me to send a link because i am a new user , and i have to post 5 replies before repying with a link . here is an example :


Master
FG00104 MP0009 PN0041

Slave
FG00104 MP0009 PN0028

Now , i need a code , that looks at the Coulms A B C combination in a Master File , then find the same AB combination in the slave file and checks , if the value in the Coulmn C is the same or has been updated . like in the above case master has been updated to PN0041 . The files , i have contains like 1000 rows .

Thanks for your help.

[austenr]

are master and slave two worksheets in the same workbook?

[austenr]

if your sheets are in different workbooks then you could use this to highlight differences. It looks at every cell and highlights differences in red.


Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettings.DesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and
Settings.DesktopDocs2.xls”)
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 3
′Highlights in red color if any changes in cells
Else
cell.Interior.ColorIndex = 0
End If
Next
set objExcel=nothing

[volkerpeter]

No , they are different files with similar kind of data , not exactly the same data , but these three coulmns are in both the files . The master data , i get from around 100 csv files , and I joined them together into one file .

The problem is i am very new to VBA , i have got some codes , with that your can compare ecxel files only with any difference in the cells and rows , but my problem is to check for this touple combination in the whole file , which in first file is like in 2nd row , 4th and 5th coulmn , and in the other file , 101th row , 10th and 11th coulmn. I hope i have been able to explain what I need .

[GTO]

Hi Peter,


Thanks GTO . i dont have the original files at my PC , but i have created an example. The Forum is not letting me to send a link because i am a new user , and i have to post 5 replies before repying with a link . here is an example :

I am not asking for a link. Below the Quick Reply box is a button - Go Advanced. After that, there is a Manage Attachments button below the Message box. It is self-explanatory thereafter.

Mark

PS - I re-read quickly, but believe we were talking about two workbooks/files. If so, zip them, so they can both go in one post.

[volkerpeter]

here is the slave example

[volkerpeter]

here is the master example . Thanks

[GTO]

Hi Peter,

I still wasn't utterly sure which way things are going, but believe I understand. Here's a shot to test against the example workbooks.

In this, the code goes in the 'slave' wb, in a Standard Module. Both of the workbooks must be open for it to run.

I can comment the code if that will benefit, but thought to first try it, so I can see if I'm "on the same page" with you.


Option Explicit

Sub PartNo_IDUpdates()
Dim _
wbMaster As Workbook, _
wksNewNumbers As Worksheet, _
wksSheet2Update As Worksheet, _
rngToUpdate As Range, _
aryCONC_ProductCable_New As Variant, _
aryCONC_ProductCable_2Update As Variant, _
x As Long, _
i As Long
'// Change to the name of the 'master' wb //
Const MASTER_NAME As String = "vbax33745#9_Master_ms01.xls"

On Error Resume Next
Set wbMaster = Workbooks(MASTER_NAME)
On Error GoTo 0

If wbMaster Is Nothing Then
MsgBox "You must have the master workbook open.", vbInformation, vbNullString
Exit Sub
End If

Set wksNewNumbers = wbMaster.Worksheets("Tabelle1")
Set wksSheet2Update = ThisWorkbook.Worksheets("Tabelle1")

With wksNewNumbers
aryCONC_ProductCable_New = _
.Range("A1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

For x = 1 To UBound(aryCONC_ProductCable_New, 1)
aryCONC_ProductCable_New(x, 1) = _
aryCONC_ProductCable_New(x, 1) & aryCONC_ProductCable_New(x, 2)
Next

With wksSheet2Update
Set rngToUpdate = .Range("A1:D" & .Cells(.Rows.Count, "A").End(xlUp).Row)
aryCONC_ProductCable_2Update = rngToUpdate.Value
End With

For x = 1 To UBound(aryCONC_ProductCable_2Update, 1)
aryCONC_ProductCable_2Update(x, 1) = _
aryCONC_ProductCable_2Update(x, 1) & aryCONC_ProductCable_2Update(x, 2)
Next

For x = 1 To UBound(aryCONC_ProductCable_2Update, 1)
For i = 1 To UBound(aryCONC_ProductCable_New, 1)

If aryCONC_ProductCable_2Update(x, 1) = aryCONC_ProductCable_New(i, 1) _
And Not aryCONC_ProductCable_2Update(x, 3) = aryCONC_ProductCable_New(i, 3) Then

aryCONC_ProductCable_2Update(x, 4) = aryCONC_ProductCable_New(i, 3)
Exit For
End If
Next
Next
rngToUpdate.Columns(4).Value = Application.Index(aryCONC_ProductCable_2Update, 0, 4)
End Sub

Hope that helps,

Mark

[volkerpeter]

hey Mark , that worked . thanks for your help

[volkerpeter]

i just had to make a very little changes

[anuph]

if your sheets are in different workbooks then you could use this to highlight differences. It looks at every cell and highlights differences in red.


Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettings.DesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and
Settings.DesktopDocs2.xls”)
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 3
′Highlights in red color if any changes in cells
Else
cell.Interior.ColorIndex = 0
End If
Next
set objExcel=nothing

Hi austen,

i am very new to excel vba, while i am trying to use your codes for my excel, but i am getting run time error:424
object required

i want all the columns of my workbook 1 to be highlighted in green rather hen only one cell.
my codes are

Sub compare()
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open“C: Users.Arun.Desktop.test.xlsm”
Set objWorkbook2 = objExcel.Workbooks.Open“C: Users.Arun.Desktop.Final PL.xlsm”
Set objWorksheet1 = objWorkbook1.Worksheets(1)
Set objWorksheet2 = objWorkbook2.Worksheets(1)
For Each Cell In objWorksheet1.UsedRange
If Cell.Value = objWorksheet2.Range(Cell.Address).Value Then
Cell.Interior.ColorIndex = 43
'Highlights in red color if any changes in cells
Else
Cell.Interior.ColorIndex = 2
End If
Next
Set objExcel = Nothing
End Sub