Compare Data in two excels

[Montice]

Hi,

I have two excel sheets. These sheets compare data based on employee id. For example employee id in excel 1 might be in row 1 and in excel 2 it might be on row 11. So my code needs to find these two row numbers and then compare all the columns.

I am writing code in VBScript. I know this forum is for excel sheets but I could not find any help in VB Script online so I though of getting help from VBA experts as this code is essentially VBA within VB Script.

I am able to find row numbers from both the excel sheets where employee ids are matching. Now I need to compare these two rows. I am using following code to compare these two sheets but I am getting error:


WS1RowNo = 1
iRow = 11 ' matching row number in second excel sheet
For Each cell In objWorksheet1.UsedRange.Rows(WS1RowNo)
If cell.Value <> objWorksheet2.Range(objWorksheet2.UsedRange.Address(iRow,objWorksheet1.Rang e(cell.address).Column)).Value Then
cell.Interior.ColorIndex = 3 'Highlights in red color if any changes in cells
Else
cell.Interior.ColorIndex = 0
End If


I previously had below code which works as long as row numbers for comparison are same else it fails(due to its failure I came up with above code):

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

Could any one please help.
Thanks,
Montice

[Bob Phillips]

Montice, can you post the full VBScript and the two sheets?

[Montice]

I am getting type mis match error as cell.value is a value from the array while the code I am comparing it with is returning the whole array.
I will share the code shortly.
Thanks
Montice

[Montice]

Hi,

I am sorry, I would be unable to share the whole code due to confidentiality issues.

Appreciate if any one could provide any directions to me.

Thansk,
Montice

[GTO]

I have two excel sheets. These sheets compare data based on employee id. For example employee id in excel 1 might be in row 1 and in excel 2 it might be on row 11. So my code needs to find these two row numbers and then compare all the columns.
...
I am able to find row numbers from both the excel sheets where employee ids are matching. Now I need to compare these two rows. I am using following code to compare these two sheets but I am getting error...


Hi,
I am sorry, I would be unable to share the whole code due to confidentiality issues.


Hi there,

At least for me, it seems hard to even tell if you are accessing two sheets from the same workbook, or a sheet from one workbook and one from another.

Regardless, whilst there may be sensitive data issues, I would suggest that you post either a workbook with data on two sheets (or zip two workbooks with data on one sheet each), wherein the data mimics your issue. Elsewise, we are stabbing too blindly. Does that make sense?

Mark

[snb]

Assuming the employee id's in column A

sub snb()
sn=sheets(1).cells(1).currentregion
sp=sheets(2).cells(1).currentregion
st=sheets(2).cells(1).currentregion.columns(1)

for j=1 to ubound(sn)
y=application.match(sn(j,1),st,0)
for jj=2 to ubound(sn,2)
if sp(y,jj)<>sn(j,jj) then sheets(1).cells(j,jj).interior.colorindex=3
next
next
end sub

[Montice]

Hi Mark,

These are two seperate workbooks. Each workbook contains one sheet which contains data. The data columns are as below:

Sheet 1 Data:
Employee_Id Age Name Sex
9999 13 Bill M
8888 52 Monika F

Sheet 2 Data:
Employee_Id Age Name Sex
8888 52 Monika F
9999 13 Bill M

Now the code need to do following:
1. Pick both the excel sheets.
2. Pick first employee fom sheet 1 (9999) and search it in sheet 2 which will appear in row number 3 (row number is including column headings)
3. Compare data of each cell of sheet 1 row 2 with sheet 2 row 3.
4. Color the sheet 1 cells if the comparison fails.
5. Move to next row of sheet 1 for comparison.
6. continue till all the rows of sheet1 are finished.

Kindly let me know if you want me to give more details of these requirements.

The code I have written is as follows:

' Declare excel, workbook and worksheet objects.
' and then proceed with the following code:
For WS1RowNo = 2 to objWorksheet1.usedrange.columns.count
WS1EmpID = objWorksheet1.Cells(WS1RowNo,1).Value
' Search for employee picked from sheet 1 in sheet 2
bSearchEmp = Fun_SearchEmployee(WS1EmpID, iRow)
If bSearchEmp Then ' Compare only if the employee id has been found in sheet2.
Call Fun_CompareData(WS1RowNo, iRow) ' Compare the data in sheet 1 and sheet 2 by passing row number of both the sheets.
End If
Next
Function Fun_SearchEmployee(ByVal EmpID, ByRef iRow)
Fun_SearchEmployee = False
Set c = objWorksheet2.usedrange.Find(EmpID,,-4163,1)
If Not c Is Nothing and (c.value = EmpID) Then ' if employee is found
iRow = c.Row ' iRow is the row number in sheet 2 which has matching employee.
Fun_SearchEmployee = True
Exit Function
End If
End Function
Function Fun_CompareData(ByVal WS1RowNo, ByVal iRow)
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
End Function



The difficulty I am facing is in compare function. As of now it works only if the employees are appearing in the same row number in both the sheets.
Considering the example I have given above, it will fail. Could any one please help me in modifying the compare function so that it can compare data in both the sheets based on the row number where employees are found.

Any help is highly appreciated.

Thanks,
Montice

[Bob Phillips]

' Declare excel, workbook and worksheet objects.
' and then proceed with the following code:
For WS1RowNo = 2 To objWorksheet1.UsedRange.Rows.Count
WS1EmpID = objWorksheet1.Cells(WS1RowNo, 1).Value
' Search for employee picked from sheet 1 in sheet 2
If Fun_SearchEmployee(WS1EmpID, iRow) Then ' Compare only if the employee id has been found in sheet2.
Call Fun_CompareData(WS1RowNo, iRow) ' Compare the data in sheet 1 and sheet 2 by passing row number of both the sheets.
Else
objWorksheet1.Cells(WS1RowNo, "A").Resize(, 4).Interior.ColorIndex = 3
End If
Next

Function Fun_SearchEmployee(ByVal EmpID, ByRef iRow)
Fun_SearchEmployee = False
Set c = objWorksheet2.UsedRange.Find(EmpID, , -4163, 1)
If Not c Is Nothing Then
If (c.Value = EmpID) Then ' if employee is found
iRow = c.Row ' iRow is the row number in sheet 2 which has matching employee.
Fun_SearchEmployee = True
End If
End If
End Function

Function Fun_CompareData(ByVal WS1RowNo, ByVal iRow)
For Each cell In objWorksheet1.UsedRange.Rows(WS1RowNo).Cells
If cell.Value <> objWorksheet2.Cells(iRow, cell.Column).Value Then
cell.Interior.ColorIndex = 3 'Highlights in red color if any changes in cells
Else
cell.Interior.ColorIndex = 0
End If
Next cell
End Function