Extracting names from an unsorted list.

[Brian H]

I have the following data table.

Name Month qs1 qs2 qs3
John Jan-07 4 5 5
John Jan-07 4 4 3
Jim Jan-07 5 5 5
Jim Jan-07 4 4 5
John Feb-07 5 5 4
John Feb-07 5 4 5
Alice Feb-07 5 5 5
Alice Feb-07 4 4 5
Jim Feb-07 5 3 3
Jim Feb-07 5 5 5

What I am trying to do is extract a list of the names. the names are in a
dynamic range call namelist. As time goes on names could be added to the
list and I would like to have an automatic list of all names for use in a
drop down list for score review.

Thoughts and Thanks!

 

[Gary''s Student]

Excel has a feature just for this kind of application - Autofilter

Just click on A1 and pull-down
Data > Filter > AutoFilter

also see:

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

 

[Toppers]

Try:

Filter=>Advanced filter=>Unique Entries

Record as macro if required and attach macro to buuton if you want to
2automate" the action.

HTH

 

[Brian H]

The review is done on a second sheet. My bad for not including more details.
On the second sheet there is a Cell in this case C2 that holds the value for
the current name under review. I was hoping to use data validation to limit
the list of names to the names found in the list to prevent look up errors
by the other users of the sheet.

 

[Ron Coderre]

Maybe something like this:

With
A dynamic range name defined as: rngMyDynList
That list refers to your source data and returns the list of all names
(excluding the column heading) that will be culled into a sorted list of all
names.

Then....on a sheet named "ListTest" that will contain the Data Validation
list source range

A1: DV_List (or any other column heading you want)

Put this ARRAY FORMULA in
A2:
=IF(COUNTA(rngMyDynList)<>0,IF(SUM(-ISERROR(MATCH(rngMyDynList,$A$1:$A1,0))),INDEX(rngMyDynList,MATCH(1,--ISERROR(MATCH(rngMyDynList,$A$1:$A1,0)),0),1),""),"")

Copy A2
Paste into A3 and down as far as you think you'll need

Create this dynamic named range, which will be the Data Validation list
Name: DV_List
Refers to: =OFFSET(ListTest!$A$1,1,0,COUNTIF(ListTest!$A:$A,"="&"?*")-1,1)

Last.....Select the cells to use Data Validation and
set the list source to "DV_List"

Example:
On the list source range:
A1: Heading
A2: Dog
A3: Dog
A4: Cat
A5: Bird
A6: Cat

On the sheet containing the DV list, the formulas return:
A1: DV_List
A2: Dog
A3: Cat
A4: Bird

and the DV dropdown list displays
Dog
Cat
Bird

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

OK....I left out a key step....I'll just repost, with corrections:
(The step I left out is the one that sorts the list)

With
A dynamic range name defined as: rngMyDynList
That list refers to your source data and returns the list of all names
(excluding the column heading) that will be culled into a sorted list of all
names.

Then....
Create a second dynamic range name
Name: MySortedList
Refers to:
=LOOKUP(MATCH(SMALL(INDEX(COUNTIF(rngMyDynList,"<"&rngMyDynList),0),ROW(ListTest!$A$1:INDEX(ListTest!$A:$A,COUNTA(rngMyDynList)))),INDEX(COUNTIF(rngMyDynList,"<"&rngMyDynList),0),0),ROW(ListTest!$A$1:INDEX($A:$A,ROWS(rngMyDynList))),rngMyDynList)

Then...on a sheet named "ListTest" that will contain the Data Validation
list source range

A1: DV_List (or any other column heading you want)

Put this ARRAY FORMULA in
A2:
=IF(COUNTA(MySortedList)<>0,IF(SUM(-ISERROR(MATCH(MySortedList,$A$1:$A1,0))),INDEX(MySortedList,MATCH(1,--ISERROR(MATCH(MySortedList,$A$1:$A1,0)),0),1),""),"")

Copy A2
Paste into A3 and down as far as you think you'll need

Create this dynamic named range, which will be the Data Validation list
Name: DV_List
Refers to: =OFFSET(ListTest!$A$1,1,0,COUNTIF(ListTest!$A:$A,"="&"?*")-1,1)

Last.....Select the cells to use Data Validation and
set the list source to "DV_List"

Example:
On the list source range:
A1: Heading
A2: Dog
A3: Dog
A4: Cat
A5: Bird
A6: Cat

On the sheet containing the DV list, the formulas return:
A1: DV_List
A2: Bird
A3: Cat
A4: Dog

and the DV dropdown list displays
Bird
Cat
Dog

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP

 

[Brian H]

BLAM!
That did the trick alright... and I even think I understand most of it

That just added a huge amount of fool proofing to my project. Many thanks!

 

[Ron Coderre]

I'm glad that worked for you.....Thanks for letting me know.

***********
Regards,
Ron

XL2002, WinXP