Top 7 Date from a range

[mangesh]

Hi,
Does anyone have an idea about how to find top 7 dates from a range?
For example
09/01/2014
09/02/2014
09/03/2014
09/04/2014
09/05/2014
09/06/2014
09/07/2014
09/08/2014
09/09/2014
09/10/2014
I would like to have a message as "Reporting Period" against 09/04/2014 through 09/10/2014.

[TheCman81]

Liek this

=LARGE($A$2:$A$11,ROW(A1))

[mangesh]

Thanks TheCman81 but it is not providing a desired result.
It is populating value against all the dates. I would like to have value against top 7 dates from a range.

[TheCman81]

I think you need to elaborate more, can you upload some sample data with expected results

Thanks

[oeldere]

in addition to the solution of TheCman81, use index/match to get the responding data next to the rank.

Or use a pivot table for that kind of work.

[mangesh]

In column A i have a date and in column B i would like to see "Reporting Period" statement against the top 7 dates.

[TheCman81]

Something like this should do the trick

Top7 Dates.xlsx

[TheCman81]

Just realised you have duplicate dates, replace this with the following array formula. Entered with Ctrl Shift Enter

=IF(ISERROR(MATCH(A2,LARGE(IF(FREQUENCY(MATCH($A$2:$A$21,$A$2:$A$21,0),ROW($A$2:$A$21)-ROW($A$2)+1),$A$2:$A$21),{1,2,3,4,5,6,7}),0)),"","Reporting Period")

[newdoverman]

This seems to work with your example.

Please Login or Register  to view this content.

[daddylonglegs]

This seems to work with your example.

I think that the 18th and 19th should be included in the reporting period, shouldn't they? Cman81's suggestion does that, or you could use this shorter version

=IF(SUM(IF(FREQUENCY(IF(A$1:A$20>A1,A$1:A$20),A$1:A$20),1))<7,"Reporting Period","")

confirm with CTRL+SHIFT+ENTER

[newdoverman]

I had a different interpretation of the problem.

[mangesh]

Thank you everyone for your help.