Help me for search and compare

[michael126]

Hi I have a vb script which compares two worksheets in two different work books. Life is green as long as it finds the same row on both sheets. If a row is missing in target my compare fails every data from that row onwards.

I have 60,000 rows of data from Colum A- R

see below example:
Source(Sheet1 in Book1)
Empl id: Name:
12345 Andrew
56789 Jacob
12345 Michael
45566 Michelle
89889 Tim
43456 Jason

Target(Sheet1 in Book2)
12345 Andrew
45566 Michelle
898989 Tim
43456 Jason
56789 Jacob

My compare script works fine unitl Jacob and fails after that. So this is what i need is my script should insert source row in in the Target and highlight red color. So that i know what are the new values it added.
any help would be greatly appreciated

 

[Michael M]

Hi Michael and Welcome to the Board
Maybe you could post your existing code and posters can have a look at it and maybe modify to suit.
I'd suggest using ExcelJeanie www.excel-jeanie-html.de

or Richard Schollars HTML maker by sending a PM to Richard requesting the download

 

[WRL]

Hi Michael126,

Perhaps publish your script so we can try and see where it goes wrong.

Can the same name occur more than once in either Source or Target?

It also seem that there are no real order to either of the sheets. Is this correct?

Do you compare on a single column only, or both the ones you show in the example, or perhaps even on all columns (A - R)?

Is the Empl ID unique in both Workbooks? (I notice that Andrew and Michael has the same Empl ID in the Source of your example, hence my question).

You mention that new rows must be inserted and highlighted in red. Should it be inserted ni the same row number on the other sheet, or can it be added at the bottom?

Which version of Excel are you using?

Sorry for all the questions - just trying to get sufficient info in one go to try and help.

 

[michael126]

Wove, Thank you all for your super fast reply.

1. EmplId and the order i goofed up, but in reality every row is a unique row in the spread sheet.
2. I need to compare the all columns(A-R)
3. Yes, the row must be inserted at the same row number on the target
4. Currently my source spread sheet contains 300 records where as Target Contains only 275 records, those 25 records spread across the spread sheet,
5. I am using Office2003 professional edition

I want make my spread sheet straight before i even run the below script.
Hopefully i answered all of your questions..

I can send my sample files if you guys can send me your email id..

Thanks in advance

I should have put my question more clearly, sorry about that.

This is the script i am using in QTP(Quick Test Professional).:
Set objWorkbook1= objExcel.Workbooks.Open("C:\QTP\Results\2010TaxUpdateSTAGE.xls")
Set objWorksheet1 = objWorkbook1.Worksheets(1)'’'select the sheet based on the index .. 1,2 ,3
Set objRange = objWorksheet1.UsedRange'’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range( "A1" )'’ select the column to sort
objRange.Sort objRange2,1,,,,,,1 ' Sort the DB values

'Sheet1 Array
m=0

a=2

For a= 2 to 33468

eXLSheet1(m,0) = trim(objWorksheet1.Cells(a,1))
eXLSheet1(m,1) = trim(objWorksheet1.Cells(a,2))
eXLSheet1(m,2) = trim(objWorksheet1.Cells(a,3))
eXLSheet1(m,3) = trim(objWorksheet1.Cells(a,4))
eXLSheet1(m,4) = trim(objWorksheet1.Cells(a,5))
eXLSheet1(m,5) = trim(objWorksheet1.Cells(a,6))
eXLSheet1(m,6) = trim(objWorksheet1.Cells(a,7))
eXLSheet1(m,7) = trim(objWorksheet1.Cells(a,8))
eXLSheet1(m,8) = trim(objWorksheet1.Cells(a,9))
eXLSheet1(m,9) = trim(objWorksheet1.Cells(a,10))
eXLSheet1(m,10) = trim(objWorksheet1.Cells(a,11))
eXLSheet1(m,11) = trim(objWorksheet1.Cells(a,12))
eXLSheet1(m,12) = trim(round(objWorksheet1.Cells(a,13),2))
eXLSheet1(m,13) = trim(round(objWorksheet1.Cells(a,14),2))
eXLSheet1(m,14) = trim(round(objWorksheet1.Cells(a,15),2))
eXLSheet1(m,15) = trim(round(objWorksheet1.Cells(a,16),2))
eXLSheet1(m,16) = trim(objWorksheet1.Cells(a,17))
eXLSheet1(m,17) = trim(objWorksheet1.Cells(a,18))
m=m+1
PassOrFail = "Fail"
Next

Set objWorkbook2= objExcel.Workbooks.Open("C:\QTP\Results\2010TaxUpdateST1.xls")
Set objWorksheet2 = objWorkbook2.Worksheets(1)'’'select the sheet based on the index .. 1,2 ,3
Set objRange = objWorksheet2.UsedRange'’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range( "A1" )'’ select the column to sort
objRange.Sort objRange2,1,,,,,,1 ' Sort the Benefits Values

'Sheet2 Array
p=0

k=2
For k=2 to 33468

eXLSheet2(p,0) = trim(objWorksheet2.Cells(k,1))
eXLSheet2(p,1) = trim(objWorksheet2.Cells(k,2))
eXLSheet2(p,2) = trim(objWorksheet2.Cells(k,3))
eXLSheet2(p,3) = trim(objWorksheet2.Cells(k,4))
eXLSheet2(p,4) = trim(objWorksheet2.Cells(k,5))
eXLSheet2(p,5) = trim(objWorksheet2.Cells(k,6))
eXLSheet2(p,6) = trim(objWorksheet2.Cells(k,7))
eXLSheet2(p,7) = trim(objWorksheet2.Cells(k,8))
eXLSheet2(p,8) = trim(objWorksheet2.Cells(k,9))
eXLSheet2(p,9) = trim(objWorksheet2.Cells(k,10))
eXLSheet2(p,10) = trim(objWorksheet2.Cells(k,11))
eXLSheet2(p,11) = trim(objWorksheet2.Cells(k,12))
eXLSheet2(p,12) = trim(round(objWorksheet2.Cells(k,13),2))
eXLSheet2(p,13) = trim(round(objWorksheet2.Cells(k,14),2))
eXLSheet2(p,14) = trim(round(objWorksheet2.Cells(k,15),2))
eXLSheet2(p,15) = trim(round(objWorksheet2.Cells(k,16),2))
eXLSheet2(p,16) = trim(objWorksheet2.Cells(k,17))
eXLSheet2(p,17) = trim(objWorksheet2.Cells(k,18))
p=p+1
PassOrFail = "Fail"
Next
'Comparing two excel sheets and writing to the results sheet
a = 0
p=0
While eXLSheet1(a,0) <> ""
If eXLSheet1(a,0)=eXLSheet2(p,0) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1COMPANY",dtGlobalSheet) = eXLSheet1(a,0)
DataTable("Env2COMPANY",dtGlobalSheet) = eXLSheet2(p,0)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1COMPANY",dtLocalSheet) = eXLSheet1(a,0)
DataTable("Env2COMPANY",dtLocalSheet) = eXLSheet2(p,0)
End If
If eXLSheet1(a,1)=eXLSheet2(p,1) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1PAYGROUP",dtGlobalSheet) = eXLSheet1(a,1)
DataTable("Env2PAYGROUP",dtGlobalSheet) = eXLSheet2(p,1)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1PAYGROUP",dtLocalSheet) = eXLSheet1(a,1)
DataTable("Env2PAYGROUP",dtLocalSheet) = eXLSheet2(p,1)
End If
If eXLSheet1(a,2)=eXLSheet2(p,2) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1PAYENDDATE",dtGlobalSheet) = eXLSheet1(a,2)
DataTable("Env2PAYENDDATE",dtGlobalSheet) = eXLSheet2(p,2)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1PAYENDDATE",dtLocalSheet) = eXLSheet1(a,2)
DataTable("Env2PAYENDDATE",dtLocalSheet) = eXLSheet2(p,2)
End If
If eXLSheet1(a,3)=eXLSheet2(p,3) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1OFF_CYCLE",dtGlobalSheet) = eXLSheet1(a,3)
DataTable("Env2OFF_CYCLE",dtGlobalSheet) = eXLSheet2(p,3)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1OFF_CYCLE",dtLocalSheet) = eXLSheet1(a,3)
DataTable("Env2OFF_CYCLE",dtLocalSheet) = eXLSheet2(p,3)
End If
If eXLSheet1(a,4)=eXLSheet2(p,4) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1PAGE_NUM",dtGlobalSheet) = eXLSheet1(a,4)
DataTable("Env2PAGE_NUM",dtGlobalSheet) = eXLSheet2(a,4)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1PAGE_NUM",dtLocalSheet) = eXLSheet1(a,4)
DataTable("Env2PAGE_NUM",dtLocalSheet) = eXLSheet2(p,4)
End If
If eXLSheet1(a,5)=eXLSheet2(p,5) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1LINE_NUM",dtGlobalSheet) = eXLSheet1(a,5)
DataTable("Env2LINE_NUM",dtGlobalSheet) = eXLSheet2(p,5)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1LINE_NUM",dtLocalSheet) = eXLSheet1(a,5)
DataTable("Env2LINE_NUM",dtLocalSheet) = eXLSheet2(p,5)
End If
If eXLSheet1(a,6)=eXLSheet2(p,6) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1SEPCHK",dtGlobalSheet) = eXLSheet1(a,6)
DataTable("Env2SEPCHK",dtGlobalSheet) = eXLSheet2(p,6)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1SEPCHK",dtLocalSheet) = eXLSheet1(a,6)
DataTable("Env2SEPCHK",dtLocalSheet) = eXLSheet2(p,6)
End If
If eXLSheet1(a,7)=eXLSheet2(p,7) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1FORM_ID",dtGlobalSheet) = eXLSheet1(a,7)
DataTable("Env2FORM_ID",dtGlobalSheet) = eXLSheet2(p,7)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1FORM_ID",dtLocalSheet) = eXLSheet1(a,7)
DataTable("Env2FORM_ID",dtLocalSheet) = eXLSheet2(p,7)
End If
If eXLSheet1(a,8)=eXLSheet2(p,8) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1PAYCHECK_NBR",dtGlobalSheet) = eXLSheet1(a,8)
DataTable("Env2PAYCHECK_NBR",dtGlobalSheet) = eXLSheet2(p,8)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1PAYCHECK_NBR",dtLocalSheet) = eXLSheet1(a,8)
DataTable("Env2PAYCHECK_NBR",dtLocalSheet) = eXLSheet2(p,8)
End If
If eXLSheet1(a,9)=eXLSheet2(p,9) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1EMPLID",dtGlobalSheet) = eXLSheet1(a,9)
DataTable("Env2EMPLID",dtGlobalSheet) = eXLSheet2(p,9)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1EMPLID",dtLocalSheet) = eXLSheet1(a,9)
DataTable("Env2EMPLID",dtLocalSheet) = eXLSheet2(p,9)
End If
If eXLSheet1(a,10)=eXLSheet2(p,10) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1NAME",dtGlobalSheet) = eXLSheet1(a,10)
DataTable("Env2NAME",dtGlobalSheet) = eXLSheet2(p,10)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1NAME",dtLocalSheet) = eXLSheet1(a,10)
DataTable("Env2NAME",dtLocalSheet) = eXLSheet2(p,10)
End If
If eXLSheet1(a,11)=eXLSheet2(p,11) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1DEPTID",dtGlobalSheet) = eXLSheet1(a,11)
DataTable("Env2DEPTID",dtGlobalSheet) = eXLSheet2(p,11)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1DEPTID",dtLocalSheet) = eXLSheet1(a,11)
DataTable("Env2DEPTID",dtLocalSheet) = eXLSheet2(p,11)
End If
If eXLSheet1(a,12)-eXLSheet2(p,12) <= 0.05Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1TOTAL_GROSS",dtGlobalSheet) = eXLSheet1(a,12)
DataTable("Env2TOTAL_GROSS",dtGlobalSheet) = eXLSheet2(p,12)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1TOTAL_GROSS",dtLocalSheet) = eXLSheet1(a,12)
DataTable("Env2TOTAL_GROSS",dtLocalSheet) = eXLSheet2(p,12)
DataTable("Env1EMPLID",dtLocalSheet) = eXLSheet1(a,9)

End If
If eXLSheet1(a,13)-eXLSheet2(p,13) <=0.05 Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1TOTAL_TAXES",dtGlobalSheet) = eXLSheet1(a,13)
DataTable("Env2TOTAL_TAXES",dtGlobalSheet) = eXLSheet2(p,13)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env2TOTAL_TAXES",dtLocalSheet) = eXLSheet1(a,13)
DataTable("Env2TOTAL_TAXES",dtLocalSheet) = eXLSheet2(p,13)
DataTable("Env1EMPLID",dtLocalSheet) = eXLSheet1(a,9)

End If
If eXLSheet1(a,14)-eXLSheet2(p,14)<= 0.05Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1TOTAL_DEDUCTIONS",dtGlobalSheet) = eXLSheet1(a,14)
DataTable("Env2TOTAL_DEDUCTIONS",dtGlobalSheet) = eXLSheet2(p,14)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1TOTAL_DEDUCTIONS",dtLocalSheet) = eXLSheet1(a,14)
DataTable("Env2TOTAL_DEDUCTIONS",dtLocalSheet) = eXLSheet2(p,14)
DataTable("Env1EMPLID",dtLocalSheet) = eXLSheet1(a,9)
End If
If eXLSheet1(a,15)-eXLSheet2(p,15) <= 0.05Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1NET_PAY",dtGlobalSheet) = eXLSheet1(a,15)
DataTable("Env2NET_PAY",dtGlobalSheet) = eXLSheet2(p,15)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1NET_PAY",dtLocalSheet) = eXLSheet1(a,15)
DataTable("Env2NET_PAY",dtLocalSheet) = eXLSheet2(p,15)
DataTable("Env1EMPLID",dtLocalSheet) = eXLSheet1(a,9)
End If
If eXLSheet1(a,16)=eXLSheet2(p,16) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1PAYCHECKDATE",dtGlobalSheet) = eXLSheet1(a,16)
DataTable("Env2PAYCHECKDATE",dtGlobalSheet) = eXLSheet2(p,16)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1PAYCHECKDATE",dtLocalSheet) = eXLSheet1(a,16)
DataTable("Env2PAYCHECKDATE",dtLocalSheet) = eXLSheet2(p,16)
End If

If eXLSheet1(a,17)=eXLSheet2(p,17) Then
DataTable.SetCurrentRow (glRow)
DataTable("Env1STATE",dtGlobalSheet) = eXLSheet1(a,17)
DataTable("Env2STATE",dtGlobalSheet) = eXLSheet2(p,17)
Else
DataTable.SetCurrentRow (lcRow)
DataTable("Env1STATE",dtLocalSheet) = eXLSheet1(a,17)
DataTable("Env2STATE",dtLocalSheet) = eXLSheet2(p,17)
DataTable("Env1EMPLID",dtLocalSheet) = eXLSheet1(a,9)
End If
glRow = glRow +1
lcRow = lcRow + 1
a = a+1
p=p+1
Wend

 

[WRL]

Hi Michael,

Thank you for the answers - it helps!

Unfortunately I don't have any answers yet, but a few comments and more questions, please:
1. Most important - I don't know QTP, neither do I use scripting. I use VBA. I am not sure if the code (language/syntax/etc) is the same. Thus, I am not exactly sure I can help you! I can try if you will, but even if I do find answers, there may be differences as to how you would implement them.

2. What do you mean you "want to make spreadsheet straight"? Your code seems to first sort both sheets. Is this what you mean?

3. You say that you DO want to compare on all columns A - R. Does this mean that only if ALL columns are the same in both, it should not be added to target? This also means that it is not only the EmplID field that could differ, but it could be any other field.

4. I notice that you use a variable PassOrFail that you set to "Fail" every time you go through the loop - in both the first two FOR loops. I notice you don't use this variable again further in the code. Thus, what is the purpose of this variable?

Regards

 

[michael126]

Hi WRL, Thanks for the reply.

You can ignore the QTP part. When i say i need to make spread sheet straight
I want both spread sheets to be in sync before my QTP can start comparing both spread sheets.

only if ALL columns are the same in both, it should not be added to target? Yes, that is correct.

QTP uses VBA script as scripting language. If you can find a solution you can write in VBA script.

Thanks