Forum Discussion
HELP !!!!! Data Sorting
You clearly are quite comfortable with Excel...I can see that you've written some very sophisticated formulas throughout that spreadsheet.
The one way in which you were NOT sophisticated is in the fundamental design of the database that serves as the heart of the analysis you're seeking to do. That is the database at the bottom of your sheet: instead of making it a single database, you've made ten, one for each source for the "on the market" and again one for each source of the "Sold". If it were a single database, all of the derivatives would be far simpler. Excel is really good at parsing single databases; we humans often get in the way by making multiple tables that are (or appear) cleaner and more understandable to us. Learn to let Excel excel at what it can do; let it do the heavy lifting of breaking apart/sorting/comparing, and doing from a single database.
"But," you object, "I need to be able to identify whether the source is Zillow or Realtor (etc)."
Easily accomplished. Add two columns to the three that are there for each of the five sources. I've started it for you in Sheet1 of the attached. A column for Source, and a column for Status. And make it an official structured Excel table. When you've done that, you can be adding new rows without needing to adjust the formulas that refer to the table.
Let me suggest you take it from here. My guess: you can now use Pivot Tables straight from this single database to accomplish all of your range comparisons, etc. Come back with questions as needed.
If continuing with your sheet 1 table, is it possible to as your inputting a lot size excel identifies that lot size to a specific range (example: you have a 0.54-acre lot for $45,000), excel identifies that to be associated with the range 0-0.99 acres. The price associated with that lot size is then inputted into a table that averages out all prices that are associated with that range 0-0.99.
See the revised attachment. I've created what we sometimes call a "business table" that can be used as the basis for a VLOOKUP to take the acreage in column B of the table and return the range associated with that.
The table looks like this, and I've named it LotSizes (as you'll see in the formula)
I added a column to the table containing this formula: =VLOOKUP([@Acres],LotSizes,2,1)
So at the top of that table now you'll see this, and the formula has been copied all the way down.
By the way, when I open the sheet I always respond to the prompts by disabling the macros--I don't usually find macros all that helpful. I've not removed them, so they're still there, and it may be that they serve a useful purpose. I prefer to let Excel's built-in functions do the work, and usually have been able to find ways to do that.