Forum Discussion

ntstestteam's avatar
New Contributor
2 years ago

Search for a value in Excel

Hi Team,


This is for Data comparison (between 2 excel sheets). I'm taking a ID value (Primary key) from Excel1 (Source data) and locating that ID value in Excel2 (Target data). Once located, comparing the columns between both the Excel Sheets. Currently comparison is working fine but taking more time to search the ID value (if that ID is located somewhere in the end of Excel)


Is there any optimized way to search for a value in Excel.


I remember, for Text files we have ReadToSymbol(SubString). Once located we can convert String positions into Lines (match(/\n/g) || '').length). Not sure if we have any such simple mechanism for Excel Search (to avoid more no .of iterations).


For Ex: If i want to search for "1234" in ID Column which is having 10,000 rows. If "1234" placed in 9000 row, then it has to iterate 9000 times which is taking more time. Is there any better way to locate the value? 


I tried DDT as well (while (! Driver.EOF() ). Taking even more time. 


Sample code: 

function Search()
var excelFile = Excel.Open("C:\\Users\\___\\Desktop\\Test_Data.xlsx");
var excelSheet = excelFile.SheetByTitle("Sheet1");

var CompareValue='1234';
var RowCount=excelSheet.RowCount;
for(var j=0;j< RowCount;j++)
var ExcelValue= excelSheet.Cell(1,j+2).Value;