Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Based List
Hi-
I am trying to create a list of unique records based on the following data. I want the list to give me Name if Date = 5/31/2007 A B C Month Name Location 4/30/2007 John Doe Region 1 5/31/2007 John Doe Region 1 4/30/2007 Jane Smith Region 2 5/31/2007 Jane Smith Region 2 4/30/2007 Mike Moore Region 1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Based List
=IF(ISERROR(SMALL(IF($A$1:$A$20=DATEVALUE("31/05/2007"),ROW($A$1:$A$20),""),ROW($A1))),"",INDEX(B$1 :B$20,N(SMALL(IF($A$1:$A$20=DATEVALUE("31/05/2007"),ROW($A$1:$A$20),""),ROW($A1)))))
Enter with Ctrl+Shift+Enter this will list names Change B$1:B$20 to A$1:A$20 if you want date HTH "jackie" wrote: Hi- I am trying to create a list of unique records based on the following data. I want the list to give me Name if Date = 5/31/2007 A B C Month Name Location 4/30/2007 John Doe Region 1 5/31/2007 John Doe Region 1 4/30/2007 Jane Smith Region 2 5/31/2007 Jane Smith Region 2 4/30/2007 Mike Moore Region 1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Based List
Maybe you could select the range and apply data|filter|autofilter.
Then you could filter to show just the 5/31/2007 dates. jackie wrote: Hi- I am trying to create a list of unique records based on the following data. I want the list to give me Name if Date = 5/31/2007 A B C Month Name Location 4/30/2007 John Doe Region 1 5/31/2007 John Doe Region 1 4/30/2007 Jane Smith Region 2 5/31/2007 Jane Smith Region 2 4/30/2007 Mike Moore Region 1 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Based List
Hi,
Set up a criteria range which includes the title of the Date field on the first row and the second row will have 5/31/2007. You then do this command Data, Filter, Advanced Filter, Unique records (checkbox) command. In the dialog box you specify the Data source including the title, the criteria range, as discussed above, and any cell in a blank area for the output. You also specify Copy to a new location. If you need more detail let me know. -- Cheers, Shane Devenshire "jackie" wrote: Hi- I am trying to create a list of unique records based on the following data. I want the list to give me Name if Date = 5/31/2007 A B C Month Name Location 4/30/2007 John Doe Region 1 5/31/2007 John Doe Region 1 4/30/2007 Jane Smith Region 2 5/31/2007 Jane Smith Region 2 4/30/2007 Mike Moore Region 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing and updating list based on criteria | Excel Discussion (Misc queries) | |||
Return value from list based on criteria | Excel Worksheet Functions | |||
add to a cell on a list based on two criteria | New Users to Excel | |||
Create a list based on single shared criteria | Excel Worksheet Functions | |||
data validation list should have opt. to select based on criteria | Excel Worksheet Functions |