Criteria Based List

[Guest]

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

 

[Guest]

=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

 

[Dave Peterson]

Maybe you could select the range and apply data|filter|autofilter.

Then you could filter to show just the 5/31/2007 dates.

 

[Guest]

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.