efficient 3 column search !? please help!

[ewat]

hi guys,

ive gotten some code to do a 3column search (ie. finding columns by matchign 3 pieces of data given)..but its a bit brute-force like - it finds the first bit, then matches the other 2...so records on the bottom will take the longest in a way

anyway..my excel sheet is around 30k rows long, so this isn't very efficient =( is there a smart way to do this? (all in vba btw)...i was thinking of maybe having a sorted column somehow?

any help greatly appreciated

 

[jimrward]

first thoughts would be to produce a unique key in another column based on what you are searching on, for instance using a concatenation of the key elements, eg Name+Init+MidInit. Then hit this column for the search if the criteria is known

 

[NateO]

Agreed, perhaps coupled with an autofilter...

 

[ewat]

wow thats a really good idea!

but, how does an autofilter speed it up?

if i concatenate everything ...ill still hve say 20k columns wont i that it will compare to?
and the last row will still take ages to search down to?

(or is the seearch a bit smarter than this)

 

[NateO]

Not with an autofilter. It'll return all of the matching data and hide the residual. Now use the special cells method -> XL visible for whatever you're contemplating doing.

 

[ewat]

oh?> you can code vba to autofilter!?

basically iknow what i WANT to find, its just a matter of finding it as fast as possible somehow

 

[NateO]

Sure, turn on your macro recorder and go to town.

Otherwise, here's an example:

http://www.mrexcel.com/board2/viewtopic.php?t=65699

 

[ewat]

ah!

ok ive got it sort of filtering the columns i want it to filter
but now now do i do some kinda work on it (im quite the vba newbie btw ..i.e. the search? all i need to do is match 3 elements (tcode, state and scode and then insert some data in tha trow)


my code now is kind of:
Sub insertion(ByVal tcode As String, ByVal scode As String, ByVal state As String, ByVal ltype As String)
Dim rFoundIt As Range

'filter what we want first
With Sheet2
Set rFoundIt = .Cells(1, 1)
rFoundIt.AutoFilter Field:=1, Criteria1:=state
rFoundIt.AutoFilter Field:=2, Criteria1:=tcode
End With

' search stuff here with visible rows
End Sub

 

[NateO]

Well, concatenate your three cells into one, in a single column. Then filter on this. You'll want a string in quotes.

 

[ewat]

oh ;P

ok how do i 'work' with my results tho (i.e. say the autofilter has given me 1 row left...) if this 'sheet 2' isnt my main one im looking at..how can i play around with it ;\

*sorry im really hopeless at explaining this*