efficient 3 column search !? please help!

ewat

New Member
Joined
Feb 3, 2004
Messages
13
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,796
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

ewat

New Member
Joined
Feb 3, 2004
Messages
13
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

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

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

New Member
Joined
Feb 3, 2004
Messages
13
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 ;)
 

ewat

New Member
Joined
Feb 3, 2004
Messages
13
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

Legend
Joined
Feb 17, 2002
Messages
9,700
Well, concatenate your three cells into one, in a single column. Then filter on this. You'll want a string in quotes. :)
 

ewat

New Member
Joined
Feb 3, 2004
Messages
13
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*
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,451
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top