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

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

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

Eler Ocean wrote:
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...
Thanks,
Mlg

--
Message posted via http://www.officekb.com

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

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

"Eler Ocean" <Eler wrote in message
...
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

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

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

Maybe you could post your code.

Tim


"Eler Ocean" <Eler wrote in message
...
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

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

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" wrote:

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

"Eler Ocean" <Eler wrote in message
...
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

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

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

"Eler Ocean" wrote in message
...
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" wrote:

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

"Eler Ocean" <Eler wrote in message
...
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

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

Charles Williams wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

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

Francois wrote:
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]
Thanks,
Mlg

--
Message posted via http://www.officekb.com

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

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(Strin g 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
"Eler Ocean" wrote:

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,
Eler

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

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

"Francois via OfficeKB.com" <u18959@uwe wrote in message
news:7b4c47ef76a69@uwe...
Charles Williams wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

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

Hi,
It worked fine.
Thanks to all,
Eler
"Eler Ocean" wrote:

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

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

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

"Eler Ocean" wrote:


Hi,
It worked fine.
Thanks to all,
Eler
"Eler Ocean" wrote:

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