Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and List Results
I have a table with a list of names and matching vendor numbers. I want to
create a macro or formula that takes an input entry cell "say CHI" and would give me a list of all names in the table (Chicago, Chicago White Sox, Chicago Bulls, City of Chicago etc.) Any suggestions? -- Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and List Results
Hi!
The easy way: Use AutoFilterCustomContains If you want something that's dynamic and uses formulas it depends on how large the dataset is that has to be searched as to whether it would be an efficient method to pursue. Biff "DSCAVOTTO" wrote in message ... I have a table with a list of names and matching vendor numbers. I want to create a macro or formula that takes an input entry cell "say CHI" and would give me a list of all names in the table (Chicago, Chicago White Sox, Chicago Bulls, City of Chicago etc.) Any suggestions? -- Dave |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and List Results
The database is currently 3000 lines long, not sorted apha and it will
continue to grow over time. How would that affect things? -- Dave "Biff" wrote: Hi! The easy way: Use AutoFilterCustomContains If you want something that's dynamic and uses formulas it depends on how large the dataset is that has to be searched as to whether it would be an efficient method to pursue. Biff "DSCAVOTTO" wrote in message ... I have a table with a list of names and matching vendor numbers. I want to create a macro or formula that takes an input entry cell "say CHI" and would give me a list of all names in the table (Chicago, Chicago White Sox, Chicago Bulls, City of Chicago etc.) Any suggestions? -- Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and List Results
Assuming you want to get all names that contain a word starting with
the letters in the input cell, as your example implied ... Assume list of names in A2:A90, input cell is C2 In D2 enter =SMALL(IF(ISERROR(SEARCH(" "&$C$2," "&A2:A90)),"",ROW(A2:A90)),ROW(1:1)) as a array formula (enter with CTL-SHIFT-ENTER) and drag down as many rows as you think you will have entries in your resultant list. this will give you row numbers of names containing words that start with the string entered in C2, and #NUM errors following the end of the list. In E2 enter =IF(ISNUMBER(D2),INDEX(A:A,D2),""), and drag down as far as the formulas go in column D. This will give you all the names that meet your criterion. If you want to get all the names that simply contain the string entered in the input cell, eliminate the two instances of " "& from the first formula so that it reads =SMALL(IF(ISERROR(SEARCH($C$2,A2:A90)),"",ROW(A2:A 90)),ROW(1:1)) You can hide column D if you don't want the #NUMs to be visible, or you can combine the two formulas into one if you don't want to use the helper column (D). Another approach is to use a macro and the custom option of autofilter to simply filter the litst to show only those names that match the input string, but that does not generate a separate list as you seemed to want. HTH DOR |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and List Results
I have checked both formulas and they both give me the numerical row that
data is residing in but not the names as you indicated in your response. Any suggestions? Appreciate the help. -- Dave "DOR" wrote: Assuming you want to get all names that contain a word starting with the letters in the input cell, as your example implied ... Assume list of names in A2:A90, input cell is C2 In D2 enter =SMALL(IF(ISERROR(SEARCH(" "&$C$2," "&A2:A90)),"",ROW(A2:A90)),ROW(1:1)) as a array formula (enter with CTL-SHIFT-ENTER) and drag down as many rows as you think you will have entries in your resultant list. this will give you row numbers of names containing words that start with the string entered in C2, and #NUM errors following the end of the list. In E2 enter =IF(ISNUMBER(D2),INDEX(A:A,D2),""), and drag down as far as the formulas go in column D. This will give you all the names that meet your criterion. If you want to get all the names that simply contain the string entered in the input cell, eliminate the two instances of " "& from the first formula so that it reads =SMALL(IF(ISERROR(SEARCH($C$2,A2:A90)),"",ROW(A2:A 90)),ROW(1:1)) You can hide column D if you don't want the #NUMs to be visible, or you can combine the two formulas into one if you don't want to use the helper column (D). Another approach is to use a macro and the custom option of autofilter to simply filter the litst to show only those names that match the input string, but that does not generate a separate list as you seemed to want. HTH DOR |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and List Results
I found my error. Thanks for your help!
-- Dave "DOR" wrote: Assuming you want to get all names that contain a word starting with the letters in the input cell, as your example implied ... Assume list of names in A2:A90, input cell is C2 In D2 enter =SMALL(IF(ISERROR(SEARCH(" "&$C$2," "&A2:A90)),"",ROW(A2:A90)),ROW(1:1)) as a array formula (enter with CTL-SHIFT-ENTER) and drag down as many rows as you think you will have entries in your resultant list. this will give you row numbers of names containing words that start with the string entered in C2, and #NUM errors following the end of the list. In E2 enter =IF(ISNUMBER(D2),INDEX(A:A,D2),""), and drag down as far as the formulas go in column D. This will give you all the names that meet your criterion. If you want to get all the names that simply contain the string entered in the input cell, eliminate the two instances of " "& from the first formula so that it reads =SMALL(IF(ISERROR(SEARCH($C$2,A2:A90)),"",ROW(A2:A 90)),ROW(1:1)) You can hide column D if you don't want the #NUMs to be visible, or you can combine the two formulas into one if you don't want to use the helper column (D). Another approach is to use a macro and the custom option of autofilter to simply filter the litst to show only those names that match the input string, but that does not generate a separate list as you seemed to want. HTH DOR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
Using Lookup on an expanding list | Excel Worksheet Functions | |||
Creating an invoice with a lookup list | Excel Discussion (Misc queries) | |||
Lookup cell value using list of worksheet names | Excel Worksheet Functions | |||
Lookup Issues / List Auto-Adjust | Excel Worksheet Functions |