Related:
- Excel search macro
- Spell number in excel without macro - Guide
- Yahoo search history - Guide
- Safe search settings - Guide
- Google.us search - Guide
- Excel mod apk for pc - Download - Spreadsheets
4 responses
buster23
Posts
15
Registration date
Monday December 29, 2008
Status
Member
Last seen
June 3, 2009
2
Dec 30, 2008 at 03:02 AM
Dec 30, 2008 at 03:02 AM
hi,
you can learn how to do your tricks using the link:
http://office.microsoft.com/en-us/excel/HA011189581033.aspx
you can learn how to do your tricks using the link:
http://office.microsoft.com/en-us/excel/HA011189581033.aspx
Here's my take on this.
Don't use a Macro.
Place your list of names (or whatever) in Column D of Sheet2.
Use a dummy/sort column with a =if(iserror(Find(Sheet1!A$1,D2)),0,1) statement (column A) and another column (B) with =B1+A2 and then use column C for =A2*B2.
Set Cell A1 on Sheet1 up as your search criteria and then set Cells A2..A100 as 1, 2, 3, 4, ... for as many matches as you think you may have up to the number of possibilities.
For range B2..B100, set the cell formula as =if(iserror(vlookup(A2,Sheet2!$c$2:$D$100,2,false)),"",vlookup(A2,Sheet2!$c$2:$D$100,2,false)
)
The formula in Column B will find the unique identifiers (1, 2, 3, etc...) in Column C of Sheet2 and return the value in Column D. The numbers will be in sequential rows for as many as you have valid logical answers. The Find function will locate the string value indicated in cell A1 of Sheet1 anywhere it occurs in the list of string values and will generate either a 1 or 0 based on the presence of the test string. The B1+C2 formula will index the valid entries by +1 every time it recognizes them in the list and the multiplying the 2 column together you will wind up with the unique identifier set 0, 0, 0, 1, 0, 0 , 2, 0, 3, 0, ... This will probably update faster than a Macro that can do the same job.
Don't use a Macro.
Place your list of names (or whatever) in Column D of Sheet2.
Use a dummy/sort column with a =if(iserror(Find(Sheet1!A$1,D2)),0,1) statement (column A) and another column (B) with =B1+A2 and then use column C for =A2*B2.
Set Cell A1 on Sheet1 up as your search criteria and then set Cells A2..A100 as 1, 2, 3, 4, ... for as many matches as you think you may have up to the number of possibilities.
For range B2..B100, set the cell formula as =if(iserror(vlookup(A2,Sheet2!$c$2:$D$100,2,false)),"",vlookup(A2,Sheet2!$c$2:$D$100,2,false)
)
The formula in Column B will find the unique identifiers (1, 2, 3, etc...) in Column C of Sheet2 and return the value in Column D. The numbers will be in sequential rows for as many as you have valid logical answers. The Find function will locate the string value indicated in cell A1 of Sheet1 anywhere it occurs in the list of string values and will generate either a 1 or 0 based on the presence of the test string. The B1+C2 formula will index the valid entries by +1 every time it recognizes them in the list and the multiplying the 2 column together you will wind up with the unique identifier set 0, 0, 0, 1, 0, 0 , 2, 0, 3, 0, ... This will probably update faster than a Macro that can do the same job.
For all those ending up here from Google and looking for alternative to vlookup... Here is one way to get it all done with a short Macro.
The trick is to use ActiveX Textbox and run a macro to filter the table of results.
WHAT THIS CODE WILL DO
This code will Filter Data as you type and will list all possible matches appearing anywhere in text being searched
- Say your data range is A5:D1000
- First row (A5:D5) has data headers
- data you need to search is in column B
Insert an ActiveX Textbox anywhere in row 1 to 4.
Under "developer > designer mode" Double click the text box
-OR-
Create new module in VBA and enter the code below. (Make sure the textbox's name matches the one defined in PRIVATE SUB)
'-------------------------
Private Sub TextBox1_Change()
Set txt = TextBox1
Range("A5:D5").AutoFilter Field:=2, Criteria1:="=*" & txt & "*", Operator:=xlAnd
End Sub
'-------------------------
To clear filter and return complete list, just clear the contents of textbox or create a macro button with following code
'-------------------------
Sub Clear_Search()
Application.ScreenUpdating = False
ActiveSheet.OLEObjects("TextBox1").Object.Value = ""
Application.ScreenUpdating = True
End Sub
'-------------------------
HOW I USE IT
Further coding will depend on what you want to do further with filtered results. I use this code for invoicing system to find products from list of 2000. Once I find the product, It is added to invoice with its price.
You can add multiple textboxes to filter different columns or you can search different columns from same textbox.
The trick is to use ActiveX Textbox and run a macro to filter the table of results.
WHAT THIS CODE WILL DO
This code will Filter Data as you type and will list all possible matches appearing anywhere in text being searched
- Say your data range is A5:D1000
- First row (A5:D5) has data headers
- data you need to search is in column B
Insert an ActiveX Textbox anywhere in row 1 to 4.
Under "developer > designer mode" Double click the text box
-OR-
Create new module in VBA and enter the code below. (Make sure the textbox's name matches the one defined in PRIVATE SUB)
'-------------------------
Private Sub TextBox1_Change()
Set txt = TextBox1
Range("A5:D5").AutoFilter Field:=2, Criteria1:="=*" & txt & "*", Operator:=xlAnd
End Sub
'-------------------------
To clear filter and return complete list, just clear the contents of textbox or create a macro button with following code
'-------------------------
Sub Clear_Search()
Application.ScreenUpdating = False
ActiveSheet.OLEObjects("TextBox1").Object.Value = ""
Application.ScreenUpdating = True
End Sub
'-------------------------
HOW I USE IT
Further coding will depend on what you want to do further with filtered results. I use this code for invoicing system to find products from list of 2000. Once I find the product, It is added to invoice with its price.
You can add multiple textboxes to filter different columns or you can search different columns from same textbox.
