Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Charting count of unique dates in a list | Charts and Charting in Excel | |||
create a filled in calendar from list of dates and notes | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |