how to find the top 20 using fuormulra

[dmasvar]

ive got 2 colums basically,

first column called ID lets' say

got these numbers
290435212000
290435212001
290435212002
290435212003
290435212004
290435212005
293712282000
293712282001
293712282002
293712282003

notice the last four digits refer to year ('ve concatenate the ID with the year), as i want to appear in a third column the top 20 values related to a particular year I speciciy, in this case 2003.

the second column are just values associated with this list above.. any value for this example..

the trick is to pull out the top 20 given a particular year,

any help appreciated

 

[acw]

Hi

Assuming that the ID data is in column A and the values are in column B then in D1 try (array entered)

=LARGE(IF(RIGHT($A$1:$A$40,4)="2003",$B$1:$B$40,0),ROW())

Adjust ranges as required, and copy down.

Tony

 

[dmasvar]

thanks ACW

just too many talented people here !

life saver.. again !