| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering using Formulas
Thanks Bob
"Bob Phillips" wrote: Select A2:A6 on the second sheet and enter this formula in the formula bar =IF(ISERROR(SMALL(IF(Sheet1!C1:C20<=SMALL(Sheet1!C 1:C20,5),ROW($A1:$A20),"") ,ROW($A1:$A20))),"", INDEX(Sheet1!A1:A20,SMALL(IF(Sheet1!C1:C20<=SMALL( Sheet1!C1:C20,5),ROW($A1:$ A20),""),ROW($A1:$A20)))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Then in B2 enter =VLOOKUP(A2,Sheet1!A1:C20,2,FALSE) in C2 enter =VLOOKUP(A2,Sheet1!A1:C20,3,FALSE) Copy B2:C2 down to B6:c6. Then copy A2:C6 to D2:F6, etc., -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Debbie D." wrote in message ... I have information is several columns. Column A contains names, column B contains addresses and column c contains rent payment. I need to be able to see the top 5 highest rents paid along with name and addresses. I can do this by using a filter and works fine. However each of columns A,B and C are repeated 12 times for Jan-Dec therefore D,E and F would now represent Feb etc. Of course each of the different months contain varying data. On a separate part of the spreadsheet I would like to be able copy using a formula to automate for another user the spreadsheet to automatically grab the top five rents, name and addresses (a grouping of three columns) for all of the single months without having to use a filter. Essentially a copy of the original figures but just showing the top 5 rents from each month. Many thanks for taking the time to read this and any and all help welcome. Hope the above explains in enough and yet not too much details. Debbie D. (UK) |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
| How to change column letters to correct ones in many formulas automatically? | Excel Worksheet Functions | |||
| how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
| How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
| Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Threaded Mode
