Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Comparing two unsorted excel columns
#1
Not Solved
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
Reply


Messages In This Thread
Comparing two unsorted excel columns - by ProTester - 09-06-2018, 08:39 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  How to Delete Rows with duplicate values in 1 columns in excel thru QTP. arpan 0 2,781 02-09-2015, 08:47 PM
Last Post: arpan
  How to get center data value in a odd number of rows and columns web table ... sai rajesh 0 2,763 11-13-2013, 10:24 PM
Last Post: sai rajesh
  Filter multiple columns in excel kamalteja 0 3,756 07-08-2013, 02:28 AM
Last Post: kamalteja
  Not able to read all the columns in a DevEx WPF grid srisrinath2006 1 2,970 05-10-2013, 03:45 AM
Last Post: sria123
  Wrong count for UsedRange rows and columns in datatable. qtpexpert 2 6,357 06-20-2012, 04:35 PM
Last Post: qtpexpert

Forum Jump:


Users browsing this thread: 1 Guest(s)