• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract dates in order (news to oldest) that meet criteria (e.g. name)

R

Rodrigo Pombo

New Member
Hi: I would be grateful if you can help me with a formula looking at a large range that meets one criteria (i.e. a name) and extract all correspondent dates BUT in order having the most recent date first in the list (extract).

Many, many thanks in advance
 
Rodrigo Pombo said:
Hi: I would be grateful if you can help me with a formula looking at a large range that meets one criteria (i.e. a name) and extract all correspondent dates BUT in order having the most recent date first in the list (extract).

Many, many thanks in advance
Click to expand...

Please upload a sample file to help us help you...
 
With Office 365 dynamic arrays one would have
= SORT( FILTER( Table1, Table1[Name]=selected), 2, 1)

Previously,
= IF( k<=SUM( SIGN(Table1[Name]=selected ) ),
INDEX( Table1, MINIFS( k, Table1[Name], selected, Table1[Date], SMALL( IF( Table1[Name]=selected, Table1[Date] ), k ) ), {1,2,3} ),
"" )
or some similar formula with the greatly loved direct cell referencing. Not for the faint-hearted, which is probably why filtering and sorting are normally manual processes.
 
Peter Bartholomew said:
With Office 365 dynamic arrays one would have
= SORT( FILTER( Table1, Table1[Name]=selected), 2, 1)

Previously,
= IF( k<=SUM( SIGN(Table1[Name]=selected ) ),
INDEX( Table1, MINIFS( k, Table1[Name], selected, Table1[Date], SMALL( IF( Table1[Name]=selected, Table1[Date] ), k ) ), {1,2,3} ),
"" )
or some similar formula with the greatly loved direct cell referencing. Not for the faint-hearted, which is probably why filtering and sorting are normally manual processes.
Click to expand...

Thank you Peter for your help. Unfortunately I'm using 2010 (company thing) so minifs won't work. But I will keep these two solutions in my catalog, thanks again.
 
Rodrigo
If you only want the dates and not the records they belong to, the formula simplifies to
= IF( k<=SUM( SIGN(Table1[Name]=selected ) ), SMALL( IF( Table1[Name]=selected, Table1[Date] ), k ), "" )
where 'k' is an index running from 1 to the number of records in the table and 'selected' is the particular name used as a filter criterion.
 
Choose your name in cell I1 and see your dates in order from cell H4.
 

Attachments

  • Chandoo42150Sample File.xlsx
    15.6 KB · Views: 7
You must log in or register to reply here.
Back
Top