Filtering using Formulas

[Debbie D.]

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)

 

[Bob Phillips]

Select A2:A6 on the second sheet and enter this formula in the formula bar

=IF(ISERROR(SMALL(IF(Sheet1!C1:C20<=SMALL(Sheet1!C1: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.]

Thanks Bob

Select A2:A6 on the second sheet and enter this formula in the formula bar

=IF(ISERROR(SMALL(IF(Sheet1!C1:C20<=SMALL(Sheet1!C1: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)