Thanks to all who read on... ok, this one is kind of a doozie... I have a spreadsheet export that i have to work from (i can't change how it is delivered) and I have to try to use logic to eliminate "duplicate" entries or rank, i'm not sure. Basically i have a list of buildings, each building may have (or not) multiple floors and each floor has a rental rate. The problem is that the report generates an entry for each rental rate associated with the building for each floor of the building. So for example if the reality is that building A has 3 floors with floor 1 =$5/sqft, floor 2 = $4, and floor 3 = $2... i get the following... for each building...

Building Floor# Rental Rate
A 1 $5
A 1 $4
A 1 $2
A 2 $5
A 2 $4
A 2 $2
A 3 $5
A 3 $4
A 3 $2
B..........................................same thing
C.......................................... you get the point

What i need to do is to be able to creat a formula that will allow me to associate the most likely rental rates with the given floor number for each building... so for all rows where building = A, and Floor # = 1, i want to return the highest rental rate in the range of rental rates associated with building A only, then if it's Floor # 2 the next highest rate, and if floor 3 the next highest rate after that... and so on.... for each unique building id in the list.

The building id's are actually 8 digit numbers, i just used letters in this example for simplicity. I've been able to RANK the rental rates using =IF(RANK(B1,$B$1:$B$6)=A1,"yes","no"), but i need to figure out a way that excel can look to column A and rank the corresponding column B rates for unique building id's only.

So for example if cells A1 thru A6 were building id 00000001 how do i get excel to recognize this as the data set to reference for the B1:B6 array in the rank function? Most importantly, i need to be able to fill this rank function down the sheet for several hundred different building id's ranging from 4 to 9 associated rows per id?

(is that clear as mud?)

oh, and i don't know anything about VBA.

I don't know if i can attach a sample to this post... don't really see an insert option here anywhere....?

Please Help!

Thanks,
Joe