Sort unique distinct dates from a range of dates

[ojaskulkarni]

Hi,

I have table (Excel Table) with a column of multiple dates. These are record creation dates.
The range of dates is for a specified fiscal year. (say, April 1, 2013 to March 31, 2014)

I want to make a list (for data validation) that will do the following:
1. Build an array of unique distinct dates
2. Build an array of only dates that occur on a Tuesday.

The reason I say an "array" is because I want it to be dynamic and expandable. The drop down (data validation) will be used to display another set of related information. But the drop down should have only unique distinct Tuesdays available for selection.
I tried various combinations of INDEX-MATCH and OFFSET etc.. but it doesn't seem to work.

{=INDEX(Data[Creation Date],MATCH(0,COUNTIF($A$1:A1,Data[Creation Date]),0))} this returns a 0.

Data is the TABLE
Creation Date is the field in the table that has about 1400+ dates.

I also tried WEEKDAY(Data[Creation Date],13) within OFFSET to get a range of Tuesdays from the range of dates.

Can someone help?

Thanks,
Ojas

 

[Somendra Misra]

Hi Ojas,

Upload a sample file with date column.

Regards,

 

[ojaskulkarni]

Done.

 

[Somendra Misra]

@ojaskulkarni

See the attached file. Green Cells are unique list and yellow cells had dropdown.

Just advise if any issue.

Regards,

 

[ojaskulkarni]

@ojaskulkarni

See the attached file. Green Cells are unique list and yellow cells had dropdown.

Just advise if any issue.

Regards,

Thank you for your help. This is perfect.

Just one question: I can replace the $A2:$A$1436 by a table range or another named range to make this list expandable. Right?

Thanks again,
Ojas

 

[Somendra Misra]

@ojaskulkarni

Yes you can do it. See the attached.

But I had copied the formula quite a few long range with dynamic drop down list, which will take care of this.

Regards,

 

[ojaskulkarni]

@ojaskulkarni

Yes you can do it. See the attached.

But I had copied the formula quite a few long range with dynamic drop down list, which will take care of this.

Regards,

Hi Somendra,

I changed the formula to this below on a new sheet and it works perfect...

=IFERROR(INDEX(Data[Creation Date],SMALL(IF(FREQUENCY(IF(WEEKDAY(IF(Data[Creation Date]>=MIN(Data[Creation Date]),IF(Data[Creation Date]<=MAX(Data[Creation Date]),Data[Creation Date])),2)=2,Data[Creation Date]),Data[Creation Date]),ROW(Data[Creation Date])-ROW('Data'!$K$2)+1),ROWS(A$2:A2))),"-")

'Data'!$K$2 is the first date cell of the column [Creation Date] in the table [Data] (Sheet name is also 'Data')

The sort for the results change if I sort the original table [Data] by the column [Creation Date].

How can I have the list always show the latest date first?

 

[Somendra Misra]

@ojaskulkarni

If you want the latest date to be first in your dropdown just sort your date column in the table from Newest to Oldest, the dropdown date will also change.

Regards,