Hi

Let's assume rank items in Sheet1 are in column A, and you don´t have any
headers in sheet A (as follows from your message), you have 2 additional
columns (Col1 and Col2) in your table, and the number of filled rows in
Sheet1 is less than 100.

Add a column to left of your table in Sheet1 - it will be new column A now.
Into Sheet1!A1 enter the formula like
=IF(B1="","",B1 & ":" & COUNTIF(B$1:B1,B1))
, and copy the formula down for at least for all rows in your table.
Hide added column.

On sheet Sheet2, p.e. to cell A1 apply data validation list with choices 1
to 5 (ranks). Select a value for a rank.
Into range A3:B3 enter headings ("Col1", "Col2")
Into cell A4 enter the formula (adjust lookup range to real one)
=IF(ISERROR(VLOOKUP($A$1 & ":" &
ROW()-3,Sheet1!$A$1:$D$100,3,0)),"",VLOOKUP($A$1 & ":" &
ROW()-3,Sheet1!$A$1:$D$100,3,0))
Into cell B4 enter the formula
=IF(A4="","",VLOOKUP($A$1 & ":" & ROW()-3,Sheet1!$A$1:$D$100,4,0))

Copy both formulas down for some reasonable amount of rows.

Now, when you select any rank value in Sheet2!A1, according rows (except
rank column) from Sheet1 are displayed immediately.

Arvi Laanemets