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.
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.