Formula/macro to retreive data based on two criteria

[afkzaman]

Hi all, everyday I get large spreadsheets containing columns named 'Property ID', 'Annual Rent', 'let Area', 'Main Tenant' and so on. Property ID is always an integer and one ID can have thousands of tenants. What I need is to find 'Main tenant' from each 'property ID' based on either 'Annual Rent' or 'let Area'

What I do: I always create a pivot table with property ID, Max Rent or Max let Area. Then I use Vlookup or Index/Match to find out 'Main tenant' by referencing either property ID or Max Rent/Let Area.

What I'm looking for: Is there any formula/macro that will retreive 'Main tenant' from each 'Property ID' based on maximum rent of 'Annual Rent' column? Is my process ok or is there any other options to solve it easily that i'm missing? I started my Internship months ago and learning excel/vba every day. Any sort of help will be highly appreciated. Thanks in advance.

 

[SpillerBD]

Sounds like a Value Filter in the Pivot Table for the Tenant should work. Filter for "Top-10" but choose Top 1.

 

[afkzaman]

Thanks for the reply. But it doesn't work, it only shows the Top 1 from all the properties, I need top 1 from each property. Also, pivot table won't show the 'Tenant Name' or 'Address', isn't it? And getting names is my main goal.

 

[SpillerBD]

You should be able to. Make sure your PivotTable is in Tabular mode and only the subtotals you need to be shown, if any.
For the Top-10 filter, you right click the row level you want to evaluate at. You should not be using the Auto-Filter but the filter from the Pivot Table.
Showing any detail is just making sure the hierarchy is respected.
Rows: PropertyID, Address, Tenant
Values: Rent
Right click the Tenant and choose the Filter option.

 

[afkzaman]

@SpillerBD,

Yes, t's working now, thank you very much. I can now create the table with required columns entirely from pivot table, great!

I have another question now: I still need to incorporate the main tenants in my master sheet where the ID's are unsorted, hundreds of ID's . I just used index match by referencing ID and tenants arrays from pivot table and retreived tenants name, that worked flawlessly as well. Do you think it is safe/convenient/ok to reference pivot table cells directly instead of creating new columns of these fields beside pivot tables and reference them? I did that yesterday and the whole sheet looks so messy.

Thank you again for your help.

 

[SpillerBD]

You can, but you should expect to type out the referencing yourself rather than selecting the range by mouse. Must use exact match.
I expect there is a better way. Just knowing what your working with makes it unlikely to give good instructions...

 

[afkzaman]

It's working accurately so far, i'll apply it like you said until i find any better way. Thank you for your reply.