Lookup and List Results

[DSCAVOTTO]

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

[Biff]

Hi!

The easy way:

Use AutoFilter>Custom>Contains

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" <DSCAVOTTO@discussions.microsoft.com> wrote in message
news:82AEA372-1167-4FCB-9503-44E171832FFF@microsoft.com...
>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

[DSCAVOTTO]

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 AutoFilter>Custom>Contains
>
> 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" <DSCAVOTTO@discussions.microsoft.com> wrote in message
> news:82AEA372-1167-4FCB-9503-44E171832FFF@microsoft.com...
> >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
>
>
>

[DOR]

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:A90)),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

[DSCAVOTTO]

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:A90)),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
>
>

[DSCAVOTTO]

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:A90)),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
>
>