How to filter and list data based on different data.

[Defoes Right Boot]

My first column is a list of surnames so non-unique data. My second is a list
of first names, again non-unique but there is no duplication of the
combination of surnames and first names. I need to list all the first names
which go with an individual surname.

eg
Brown John
Brown Peter
Brown Michael
Smith John
Smith David
Jones Peter
Jones David

(but about 500 rows altogether) and I need to be able to, using the above
example, enter Smith in a cell and get a list showing "John" and "David", or
enter Jones and get "Peter" and "David"

Can someone help? Please?

[Debra Dalgleish]

You could use an Advanced Filter to extract the names. There are
instructions in Excel's help, and here:

http://www.contextures.com/xladvfilter01.html

Then, use en event procedure to automate the filter. For an example, see:

http://www.contextures.com/excelfiles.html

Under filters, look for: Phone List for Selected Name
Instead using all the headings, just use FirstName.
In the code, change the CopyToRange to:

CopyToRange:=wsD.Range("C6")


Defoes Right Boot wrote:
> My first column is a list of surnames so non-unique data. My second is a list
> of first names, again non-unique but there is no duplication of the
> combination of surnames and first names. I need to list all the first names
> which go with an individual surname.
>
> eg
> Brown John
> Brown Peter
> Brown Michael
> Smith John
> Smith David
> Jones Peter
> Jones David
>
> (but about 500 rows altogether) and I need to be able to, using the above
> example, enter Smith in a cell and get a list showing "John" and "David", or
> enter Jones and get "Peter" and "David"
>
> Can someone help? Please?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Bernie Deitrick]

Defoes,

With your list of last names in column A, and your first names in column B,
enter the desired last name in cell D1. Then in E1, enter 1, E2, enter 2,
etc, and in cell F1, array enter (enter using Ctrl-Shift-Enter) the
following formula.

=IF(ISERROR(INDEX(B:B,SMALL(IF($A$1:$A$1000=$D$1,ROW($A$1:$A$1000),100000),E
1))),"",INDEX(B:B,SMALL(IF($A$1:$A$1000=$D$1,ROW($A$1:$A$1000),100000),E1)))

Watch line wraps, which should be taken out.

If you have problems getting it to work, contact me privately, and I will
send you a working version.

HTH,
Bernie
MS Excel MVP


"Defoes Right Boot" <DefoesRightBoot@discussions.microsoft.com> wrote in
message news:93C5603C-A718-43F9-8ACB-8C7C6736170F@microsoft.com...
> My first column is a list of surnames so non-unique data. My second is a
list
> of first names, again non-unique but there is no duplication of the
> combination of surnames and first names. I need to list all the first
names
> which go with an individual surname.
>
> eg
> Brown John
> Brown Peter
> Brown Michael
> Smith John
> Smith David
> Jones Peter
> Jones David
>
> (but about 500 rows altogether) and I need to be able to, using the above
> example, enter Smith in a cell and get a list showing "John" and "David",
or
> enter Jones and get "Peter" and "David"
>
> Can someone help? Please?

[Defoes Right Boot]

Thanks Bernie that's absolutely spot on, works a treat!

Thanks for your advice too Debra - a handy link.

"Bernie Deitrick" wrote:

> Defoes,
>
> With your list of last names in column A, and your first names in column B,
> enter the desired last name in cell D1. Then in E1, enter 1, E2, enter 2,
> etc, and in cell F1, array enter (enter using Ctrl-Shift-Enter) the
> following formula.
>
> =IF(ISERROR(INDEX(B:B,SMALL(IF($A$1:$A$1000=$D$1,ROW($A$1:$A$1000),100000),E
> 1))),"",INDEX(B:B,SMALL(IF($A$1:$A$1000=$D$1,ROW($A$1:$A$1000),100000),E1)))
>
> Watch line wraps, which should be taken out.
>
> If you have problems getting it to work, contact me privately, and I will
> send you a working version.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Defoes Right Boot" <DefoesRightBoot@discussions.microsoft.com> wrote in
> message news:93C5603C-A718-43F9-8ACB-8C7C6736170F@microsoft.com...
> > My first column is a list of surnames so non-unique data. My second is a
> list
> > of first names, again non-unique but there is no duplication of the
> > combination of surnames and first names. I need to list all the first
> names
> > which go with an individual surname.
> >
> > eg
> > Brown John
> > Brown Peter
> > Brown Michael
> > Smith John
> > Smith David
> > Jones Peter
> > Jones David
> >
> > (but about 500 rows altogether) and I need to be able to, using the above
> > example, enter Smith in a cell and get a list showing "John" and "David",
> or
> > enter Jones and get "Peter" and "David"
> >
> > Can someone help? Please?
>
>
>