How to create a list of unique dates?
 

[List of dates]
14-Jun,14-Jun,17-Jun,15-Jun,23-Jun,16-Jun,26-Jun,17-Jun,05-Jul,19-Jun

There is a list of dates under column A, does anyone have any suggestion on
how to create a list of unique dates as show below? but I don't want to use
Data, Filter, Advanced Filter. Does anyone have any suggestions?
Thank for any suggestions
Eric

[Unique dates]
14-Jun,15-Jun,16-Jun,17-Jun,19-Jun,23-Jun,26-Jun,05-Jul

How to create a list of unique dates?
 

Hi Eric:

In B1 thru B10:

14-Jun
14-Jun
17-Jun
15-Jun
23-Jun
16-Jun
26-Jun
17-Jun
5-Jul
19-Jun

In A1 put a 1
In A2 put:

=IF(COUNTIF($B$1:B2,B2)1,"",ROW()) and copy down to see:

1 14-Jun
14-Jun
3 17-Jun
4 15-Jun
5 23-Jun
6 16-Jun
7 26-Jun
17-Jun
9 5-Jul
10 19-Jun

notice duplicates have blanks in col A

in C1 put:

=VLOOKUP(SMALL(A$1:A$10,ROW()),$A$1:$B$10,2) and copy down to see:

1 14-Jun 14-Jun
14-Jun 17-Jun
3 17-Jun 15-Jun
4 15-Jun 23-Jun
5 23-Jun 16-Jun
6 16-Jun 26-Jun
7 26-Jun 5-Jul
17-Jun 19-Jun
9 5-Jul
10 19-Jun

All items in C are unique. This technique will work with dates, numbers,
words, etc.
--
Gary''s Student - gsnu200729


"Eric" wrote:

[List of dates]
14-Jun,14-Jun,17-Jun,15-Jun,23-Jun,16-Jun,26-Jun,17-Jun,05-Jul,19-Jun

There is a list of dates under column A, does anyone have any suggestion on
how to create a list of unique dates as show below? but I don't want to use
Data, Filter, Advanced Filter. Does anyone have any suggestions?
Thank for any suggestions
Eric

[Unique dates]
14-Jun,15-Jun,16-Jun,17-Jun,19-Jun,23-Jun,26-Jun,05-Jul

How to create a list of unique dates?
 

With this list in A1:A10
14-Jun
14-Jun
17-Jun
15-Jun
23-Jun
16-Jun
26-Jun
17-Jun
5-Jul
19-Jun

Try this ARRAY FORMULA:
C1:
=SMALL(IF(ISNUMBER($A$1:$A$10),IF(ROW($A$1:$A$10)= MATCH($A$1:$A$10,$A$1:$A$10,0),$A$1:$A$10)),ROW())

Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just
pressing [Enter].

Copy C1 and paste the formula into C2 and down as far as you need

These automatically sorted results will be listed in C1:C8
14-Jun
15-Jun
16-Jun
17-Jun
19-Jun
23-Jun
26-Jun
5-Jul

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

XL2002, WinXP


"Eric" wrote:

[List of dates]
14-Jun,14-Jun,17-Jun,15-Jun,23-Jun,16-Jun,26-Jun,17-Jun,05-Jul,19-Jun

There is a list of dates under column A, does anyone have any suggestion on
how to create a list of unique dates as show below? but I don't want to use
Data, Filter, Advanced Filter. Does anyone have any suggestions?
Thank for any suggestions
Eric

[Unique dates]
14-Jun,15-Jun,16-Jun,17-Jun,19-Jun,23-Jun,26-Jun,05-Jul

How to create a list of unique dates?
 

=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(Dates,Dates,0), MATCH(Dates,Dates,0))0,ROW(INDIRECT("1:"&ROWS(Dat es)))),ROWS($1:1))),"",INDEX(Dates,SMALL(IF(FREQUE NCY(MATCH(Dates,Dates,0),MATCH(Dates,Dates,0))0,R OW(INDIRECT("1:"&ROWS(Dates)))),ROWS($1:1))))

ctrl+shift+enter, not just enter


"Eric" wrote:

[List of dates]
14-Jun,14-Jun,17-Jun,15-Jun,23-Jun,16-Jun,26-Jun,17-Jun,05-Jul,19-Jun

There is a list of dates under column A, does anyone have any suggestion on
how to create a list of unique dates as show below? but I don't want to use
Data, Filter, Advanced Filter. Does anyone have any suggestions?
Thank for any suggestions
Eric

[Unique dates]
14-Jun,15-Jun,16-Jun,17-Jun,19-Jun,23-Jun,26-Jun,05-Jul

How to create a list of unique dates?
 

On Thu, 14 Jun 2007 17:39:00 -0700, Eric
wrote:

[List of dates]
14-Jun,14-Jun,17-Jun,15-Jun,23-Jun,16-Jun,26-Jun,17-Jun,05-Jul,19-Jun

There is a list of dates under column A, does anyone have any suggestion on
how to create a list of unique dates as show below? but I don't want to use
Data, Filter, Advanced Filter. Does anyone have any suggestions?
Thank for any suggestions
Eric

[Unique dates]
14-Jun,15-Jun,16-Jun,17-Jun,19-Jun,23-Jun,26-Jun,05-Jul




Here's one way.

With your dates in a Named Range called Dates (e.g. A2:A11), enter the
following:

B2: =MIN(Dates)
B3:
=IF(AND(B2<"",COUNTIF(Dates,"<="&B2)<COUNT(Dates) ),
SMALL(Dates,1+COUNTIF(Dates,"<="&B2)),"")

Fill down as far as required.

Note that this will return a sorted list, as you gave in your example.
--ron

Thank everyone very much for suggestions
 

Thank everyone very much for suggestions
Eric

"Eric" wrote:

[List of dates]
14-Jun,14-Jun,17-Jun,15-Jun,23-Jun,16-Jun,26-Jun,17-Jun,05-Jul,19-Jun

There is a list of dates under column A, does anyone have any suggestion on
how to create a list of unique dates as show below? but I don't want to use
Data, Filter, Advanced Filter. Does anyone have any suggestions?
Thank for any suggestions
Eric

[Unique dates]
14-Jun,15-Jun,16-Jun,17-Jun,19-Jun,23-Jun,26-Jun,05-Jul

How to create a list of unique dates?
 

Gary''s wrote on Thu, 14 Jun 2007 18:49:00 -0700:

Gs In B1 thru B10:


Gs In A1 put a 1
Gs In A2 put:

Gs =IF(COUNTIF($B$1:B2,B2)1,"",ROW()) and copy down to see:

Another quick and dirty approach is to put sequential integers
in col A and =rand() in column B, sort B (extended) and then
have C1= A1 + some date etc. Format the resulting column as
date.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not