Select 50 greatest values with column matching

B

bobbly_bob

Alrighty, not sure how excatly to put this.

We have a database with about 4000 entries in it dating back over about
2 years.

There are 5 people using a macro to dump info into this sheet all
throughout the year.

Say there is cell a1 and the table is a6:c4000. column A contains the
values TG, GT or GB, randomly spread out throughout the sheet and every
cell in a6:a4000 will contain one of those.

If column B contains the date that these entries were made, I need to
use column B to find the 50 most recent entries where the value in
column a matches the value put into cell a1 (either TG, GT or GB) and
return the corresponding value from column C
 
M

Max

One play which caters for ties / multiple ties in the dates col for any of
the 3 values: TG, GT or GB ..

Source table in A6:C4000
In A1 will be the input, eg: GT

Place in D6: =IF($A$1="","",IF(A6=$A$1,B6-ROW()/10^10,""))
Copy D6 to D4000

Place in E6:
=IF(ROW(A1)>COUNT($D$6:$D$4000),"",INDEX(A$6:A$4000,MATCH(LARGE($D$6:$D$4000,ROW(A1)),$D$6:$D$4000,0)))
Copy E6 to G6, fill down by at least 50 rows, say down to G100? Format col F
as dates. Hide away col D. Cols E to G will return a descending sort (by
date col) of only the lines where A6:A4000 contains the input in A1. For the
required "50 most recent entries", just read off the first 50 lines or more
in cols E to G (from row6 down), depending on whether there are ties /
multiple ties in the dates for the input in A1.
 
B

Bob Phillips

Sort it by column B descending?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Top