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

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

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

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