Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select 50 greatest values with column matching
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select 50 greatest values with column matching
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$400 0,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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select 50 greatest values with column matching
Sort it by column B descending?
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct issues | Excel Worksheet Functions | |||
Listing variable names for 3 greatest values in a column? | Excel Worksheet Functions | |||
Column matching - sorting. Fairly hard problem, I think. | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Select top 5 in column A, sum values in B | Excel Worksheet Functions |