Find matching value between values in 2 tables

[wheelercha]

I have this script that uses 2 For Loops to loop through the data in one column of one sheet and compare it to the first column of another sheet. Basically it is trying to find a match on the name, if it finds it in the other sheet it continues forward and does other comparisons.

The issue is that it's not real fast! Is there an alternate method to compare? Or am I stuck with this.

Thanks- Charles

[sreekanth chilam]

Hi,

Please paste your code here, so that we could come back to you with right solution quickly.

[rdemers25]

Maybe read each column of the Table into an array and then do the same compare in the arrays. It would probably be a lot less time reading the values from the array vs. the excel file (expecially since it sounds like you are doing it multiple times).

[wheelercha]

Code:

Dim rowcount, i, j, CatMatchFound
CatMatchFound = false
rowcount = datatable.GetSheet( "CategoryReportData").GetRowCount
'***Include 1 space after the last word in CategoryVal(ex. "Uniforms_") the _ represents a space****
        For i = 1 to rowcount
                 CategoryVal=datatable.Value("Category", "CategoryReportData")
                  CategoryTot = datatable.Value("Total", "CategoryReportData")
                  'msgbox (CategoryVal)
                   rowcountofcompare = datatable.GetSheet( "Check Detail Lines on Category Report").GetRowCount
                             For j = 1  to rowcountofcompare
                                    'msgbox( datatable.GetSheet( "Check Detail Lines on Category Report").GetRowCount)
                                     CompareVal =datatable.Value("Category","Check Detail Lines on Category Report")
                                     CompareTot =datatable.Value("Total", "Check Detail Lines on Category Report")
                                     ' msgbox(CompareVal)
                                                    If CategoryVal= CompareVal Then
                                                         CatMatchFound = true
                                                               If  CategoryTot = CompareTot Then
                                                                    Print "Match on Category Total Report :  "&(CategoryVal) &"  $" & (CategoryTot)& _
                                                                     " matches " &(CompareVal)&"  $" &(CompareTot)
                                                                      Reporter.ReportEvent 0, "Category Report Check" , "The Category and Total displayed on the report matches expected results."
                                                                     'Msgbox (rowcount) & " rows "&(CategoryVal)
                                                                           Else
                                                                                 'Print "Category Total report also included other invoices for this category:  " &(CategoryVal)
                                                                                  Print " WARNING : Partial match found. Please verify that the Category being "
                                                                                  Print "                 added does not exist and re-run the test. "
                                                                                  Print " *****************************************************************************************************"
                                                                                  Print "  Detail : The Category Total Report displays "&(CategoryVal) &"  $" & (CategoryTot)
                                                                                  Print "                     does not match expected values:  " &(CompareVal)&"  $" &(CompareTot)
                                                                                  Print " *****************************************************************************************************"
                                                                                  Reporter.ReportEvent 3, "Category Report Check" , "The Category and Totals displayed on the report do not match expected results. " & _
                                                                                    "The Category Total Report displays  "&(CategoryVal) &"  $" & (CategoryTot) & _
                                                                                    " which is not matching the expected values:  " &(CompareVal)&"  $" &(CompareTot)
                                                                                 'msgbox (CategoryVal)
                                                                  End If  
                                                                        
                                                   End If
                                                   DataTable.GetSheet("Check Detail Lines on Category Report").SetNextRow
                                            
                               Next
                                DataTable.GetSheet( "CategoryReportData").SetNextRow 'Insert Runtime datatable
       Next
                If (CatMatchFound = false) Then
                            Print "********************************************************************************************************"
                            Print "No matches found for Category - this message only displays if the Category.Value on the "
                            Print "Check Detail Lines on Category Report action did not match due to before and/or after     "
                            Print "spaces with each value. Correct by adding a space before or after the value and re-run    "
                            Print "********************************************************************************************************"
                             Reporter.ReportEvent 1, "Category Report Check" , "Actual does not match expected results for a match on Category"
               End If