| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting names from an unsorted list.
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
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 -- Gary's Student gsnu200708 "Brian H" wrote: 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
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" wrote: 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
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. "Gary''s Student" wrote in message ... 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 -- Gary's Student gsnu200708 "Brian H" wrote: 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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
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(rng MyDynList,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 "Brian H" wrote: 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
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(rngMy DynList,"<"&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(MyS ortedList,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 "Ron Coderre" wrote: 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(rng MyDynList,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 "Brian H" wrote: 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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
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" wrote in message ... 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(rngMy DynList,"<"&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(MyS ortedList,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 "Ron Coderre" wrote: 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(rng MyDynList,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 "Brian H" wrote: 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! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting names from an unsorted list.
I'm glad that worked for you.....Thanks for letting me know.
*********** Regards, Ron XL2002, WinXP "Brian H" wrote: 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" wrote in message ... 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(rngMy DynList,"<"&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(MyS ortedList,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 "Ron Coderre" wrote: 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(rng MyDynList,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 "Brian H" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extracting names | Excel Discussion (Misc queries) | |||
| One list unsorted, but two groups - I need to rank in each group | Excel Discussion (Misc queries) | |||
| Percentage calculations from an unsorted two column list | Excel Discussion (Misc queries) | |||
| lookup unsorted list | Excel Discussion (Misc queries) | |||
| An unsorted list in vlookup | Excel Worksheet Functions | |||
Linear Mode
