How to filter and list data based on different data.
 

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?

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

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

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,R OW($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" wrote in
message ...
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?

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,R OW($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" wrote in
message ...
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?