Is there any efficent method for searching in a large excel sheet

[Guest]

Hi,

I want to search an excel sheet of around 4000 records.Now I am using file
searching.

But for each data,searching will take around 10 sec.I want to search for
around 2000 records.Then it will take 2000*10sec=5 hrs.

Is database searching efficent than file searching or there is any other
method.

Please reply

Thanks,
Mlg

 

[Francois via OfficeKB.com]

Hi,

I want to search an excel sheet of around 4000 records.Now I am using file
searching.

But for each data,searching will take around 10 sec.I want to search for
around 2000 records.Then it will take 2000*10sec=5 hrs.

Is database searching efficent than file searching or there is any other
method.

Please reply

Excel is not the best answer for database use, however I am curious why it
seems to take 10 secs to do a find...

 

[Charles Williams]

If you sort your data and use the VLOOKUP() function it will be extremely
fast

Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

 

[Tim Williams]

Your search times seem very slow: 4000 records isn't a large number.

Maybe you could post your code.

Tim

 

[Guest]

Hi
First up all thanks for giving information about this function.
I used it in the code and it working very fast.But the problem is that for
some data Vlookup fails.Vb.net is throwing an exception saying
"VLookup method of WorksheetFunction class failed".
The data for which it fails is random.C-29601,C-D1883
Mainly for these two datas it fails.All of my search data wiil be of this
type.

SnomedValues(0) = xlApp.WorksheetFunction.VLookup(Search_SnomedStr,
xlsnomed_WS.Range("B2:F2334"), 4, True)
I am passing the search data to search_Snomedstr

 

[Charles Williams]

VLOOKUP tries to return #N/A if the data is sorted ascending and the value
you are looking for is smaller than the first value.
If you use Application.Worksheetfunction.Vlookup this raises an error, if
you use Application.VLOOKUP you get an error value (XLErrNA) returned.

Note that for sorted ascending if there is no match found but the value is
greater than the first value VLOOKUP returns the largest value that is less
than or equal to lookup_value.

For sorted ascending if more than one match is found VLOOKUP returns the
last one.

regards
Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

 

[Francois via OfficeKB.com]

VLOOKUP tries to return #N/A if the data is sorted ascending and the value
you are looking for is smaller than the first value.
If you use Application.Worksheetfunction.Vlookup this raises an error, if
you use Application.VLOOKUP you get an error value (XLErrNA) returned.

Note that for sorted ascending if there is no match found but the value is
greater than the first value VLOOKUP returns the largest value that is less
than or equal to lookup_value.

For sorted ascending if more than one match is found VLOOKUP returns the
last one.

regards

It returns the FIRST one.



It returns the FIRST one

Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

Hi
First up all thanks for giving information about this function.

[quoted text clipped - 35 lines]

 

[Francois via OfficeKB.com]

VLOOKUP tries to return #N/A if the data is sorted ascending and the value
you are looking for is smaller than the first value.

[quoted text clipped - 9 lines]

regards

It returns the FIRST one.

It returns the FIRST one

Charles

sorry, I didn't mean to double statement

_________________________________________

[quoted text clipped - 6 lines]

 

[Guest]

Hi,
This is my code
Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExecute.Click

Dim condvalues(1) As String
condvalues = Snomed_Search(xldata)
End sub

Function Snomed_Search(ByVal Search_SnomedStr As String) As String()

SnomedValues(0) = xlApp.WorksheetFunction.VLookup(Search_SnomedStr,
xlsnomed_WS.Range("B2:F2334"), 4, True)
Return SnomedValues

End Sub
All the search data have a hypen ,eg:5048-4,L-44530

But for some search datas like
C-D1883,C-E1020,DD-64521,C-29061,C-96010,G-A119,D0-70021..It is throwing
exception

System.Runtime.InteropServices.COMException (0x800A03EC): VLookup method of
WorksheetFunction class failed
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.WorksheetFunction.VLookup(Object Arg1, Object Arg2, Object Arg3,
Object Arg4)

Please reply
Regards,
Midhunlal G

 

[Charles Williams]

Hi Francois,

Not sure which statement you are disagreeing with, but if you test it you
will find that:

sorted ascending VLOOKUP(val,array,returncol) returns the last match found,
or the largest value >= val if there is no match
or #N/A if val < the first element of array.
or the last value in array if val> the last element of array
(MSoft's particular implementation of Binary Search)

but VLOOKUP(val,array,returncol,FALSE) returns the first match found,
or #N/A if no match is found.
(linear search from start to end)

regards
Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

VLOOKUP tries to return #N/A if the data is sorted ascending and the value
you are looking for is smaller than the first value.
If you use Application.Worksheetfunction.Vlookup this raises an error, if
you use Application.VLOOKUP you get an error value (XLErrNA) returned.

Note that for sorted ascending if there is no match found but the value is
greater than the first value VLOOKUP returns the largest value that is
less
than or equal to lookup_value.

For sorted ascending if more than one match is found VLOOKUP returns the
last one.

regards

It returns the FIRST one.



It returns the FIRST one

Charles
_________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

Hi
First up all thanks for giving information about this function.

[quoted text clipped - 35 lines]

Thanks,
Mlg

 

[Guest]

Hi,
It worked fine.
Thanks to all,
Eler

 

[Guest]

Hi
I want to use vlookup to search in a workbook contains more than 1
worksheets.In this case how i refer 2 worksheets in the vlookup.

Thanks and Regards,
Eler