VBA Code to get unique and update Validation

[Sahil123]

Hi All,

Im hoping you can help me this.

I have a sheet called Index..On that sheet I have a table called RawData with a Date Range.

Each day This Date column gets filled in and that column will have duplicate dates in there as I update this column several times for each team.

I have a separate heading called DailyDates, WeeklyDates, MonthlyDates across G1:I1 and a data validation in J1.I have this data validation formula in J1...
=IF($B$1=1,DailyDates,IF($B$1=2,WeeklyDates,MonthlyDates))

What I want to do is get a unique list of dates for daily dates from the date range and then for the week get the Monday date for that week so that the week should always have a Mondays Date. In the Month Section it should have the unique month from the daily date range. Once this has been, I want to populate the data validation which should pick up the right named range..

I really hope this makes sense

This is what it should look like

Table names - RawData
[TABLE="width: 208"]
<tbody>[TR]
[TD]Dates
[/TD]
[TD]Team
[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2016
[/TD]
[TD]Team1
[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2016
[/TD]
[TD]Team2
[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2016
[/TD]
[TD]Team3
[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2016
[/TD]
[TD]Team4
[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2016
[/TD]
[TD]Team5
[/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2016
[/TD]
[TD]Team1
[/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2016
[/TD]
[TD]Team2
[/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2016
[/TD]
[TD]Team3
[/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2016
[/TD]
[TD]Team4
[/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2016
[/TD]
[TD]Team5
[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2016
[/TD]
[TD]Team1
[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2016
[/TD]
[TD]Team2
[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2016
[/TD]
[TD]Team3
[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2016
[/TD]
[TD]Team4
[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2016
[/TD]
[TD]Team5
[/TD]
[/TR]
[TR]
[TD="align: right"]20/09/2016
[/TD]
[TD]Team1
[/TD]
[/TR]
[TR]
[TD="align: right"]20/09/2016
[/TD]
[TD]Team2
[/TD]
[/TR]
[TR]
[TD="align: right"]20/09/2016
[/TD]
[TD]Team3
[/TD]
[/TR]
[TR]
[TD="align: right"]20/09/2016
[/TD]
[TD]Team4
[/TD]
[/TR]
[TR]
[TD="align: right"]20/09/2016
[/TD]
[TD]Team5
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 288"]
<tbody>[TR]
[TD]DailyDates
[/TD]
[TD]WeeklyDates
[/TD]
[TD]MonthlyDates
[/TD]
[/TR]
[TR]
[TD]15/09/2016
[/TD]
[TD]12/09/2016
[/TD]
[TD]Sep 16
[/TD]
[/TR]
[TR]
[TD]16/09/2016
[/TD]
[TD]19/09/2016
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/09/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The DataValidation should then pick up the correct named dynamic range depending on what option was selected

 

[Marcelo Branco]

Or

If dictWeek.Count Then
        .Range("L1").Value = 2
        .Range("J1").Value = Application.Max(dictWeek.keys)
End If

M.

 

[Sahil123]

Thank you - really appreciate what you did for me

thank you

 

[Marcelo Branco]

You are welcome. Glad to help.

M.