Micro Focus QTP (UFT) Forums
Comparing two unsorted excel columns - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: Comparing two unsorted excel columns (/Thread-Comparing-two-unsorted-excel-columns)

Comparing two unsorted excel columns - ProTester - 09-06-2018

Hi everyone,

I have a script that compares two unsorted columns (in 2 differtent sheets) using .match.
If match gives me the row number then I check if columns are equal.

Is there a faster way?

Number of rows could be 20'000 to 200'000


Code:
Code:
For i = 1 To rowCount1 Step 1
   
    x = objXL.Application.Match(objMyWorksheet1.Cells(i,1).value, objXL.Sheets(sheet2)Range("A1:A"&rowCount2),0)
     If not isNumeric(x) then
     objMyWorksheet1.Cells(i,1).Interior.ColorIndex = 6
  

     Else

    
      For z = 2 To columnCount1 Step 1
      rowA= trim(objMyWorksheet1.Cells(i,z).value)
      rowB= trim(objMyWorksheet2.Cells(x,z).value)
       If isnumeric(rowA) then rowA= Cdbl(rowA)
       If isnumeric(rowB) then rowB= Cdbl(rowB)
    
    
       If rowA <> rowBThen Call CheckRow(objMyWorksheet1,objMyWorksheet2,i,x,z)
    
      Set rowA= nothing
      Set rowB= nothing
     next
  End if
  
  next

CheckRow(objMyWorksheet1,objMyWorksheet2,i,x,z) is required because I need to see if there are some specific values that I have to ignore.
I'd like to speed up this part (and if exists, to use a faster way than match)



Code:
Code:
For z = 2 To columnCount1 Step 1
      rowA= trim(objMyWorksheet1.Cells(i,z).value)
      rowB= trim(objMyWorksheet2.Cells(x,z).value)
       If isnumeric(rowA) then rowA= Cdbl(rowA)
       If isnumeric(rowB) then rowB= Cdbl(rowB)
    
    
       If rowA <> rowBThen Call CheckRow(objMyWorksheet1,objMyWorksheet2,i,x,z)

Thank you

RE: Comparing two unsorted excel columns - Ankur - 09-07-2018

Thanks for keeping the forum clean and posting in the right section.

Can you illustrate with the help of an example data what you are trying to achieve? 

I didn't really get this -


Quote:If match gives me the row number then I check if columns are equal.


RE: Comparing two unsorted excel columns - ProTester - 09-13-2018

Hi,

I've loaded two Attachments.

for every key in KEY_Column.
If key in SHEET A exists in SHEET B then check the entire row.

Code:
x = objXL.Application.Match(objMyWorksheet1.Cells(i,1).value, objXL.Sheets(sheet2)Range("A1:A"&rowCount2),0)

this code let me get the row number of the found key in SHEET B.
If I find a match then I check Attribute One, Two, Three, Four, etc... with this code:
Code:
For z = 2 To columnCount1 Step 1
      rowA= trim(objMyWorksheet1.Cells(i,z).value)
      rowB= trim(objMyWorksheet2.Cells(x,z).value)
       If isnumeric(rowA) then rowA= Cdbl(rowA)
       If isnumeric(rowB) then rowB= Cdbl(rowB)
    
    
       If rowA <> rowBThen Call CheckRow(objMyWorksheet1,objMyWorksheet2,i,x,z)

What I am asking if there is a faster way to do the Attributes check rather than a For cycle.

Sorry, english is not my language.

I hope I cleared my issue.
Thank you