This video has several layers. The ultimate goal is to create a table of summaries from a dataset that has granular data. One layer is the creation of the named ranges.
You’ll see that I set up the named ranges and didn’t remove the blank rows or do anything to clean up the area. This was a conscious decision that analysts often have to make. That’s the layer that’s critical and invisible.
The Right Way or the Good Enough Way
The right way would have been to clean up the data. However, the good enough way gets us going much faster. The difference between the two is understanding how much maintenance and updates are in the future. If a job is one-and-done; or, there will be infrequent changes, then go for the good enough way. If there will be frequent additions and changes, then a solution has to be done the right way.
So, we’re talking now about the overarching process that the tool (Excel) exists within. In this case, the process tells us that the source data will see infrequent changes. So, the good enough solution works … and we skip the cleanup and go straight for the knockout blow! (See the cool fireball effect at 5:50)
Check out the video and see the glory of the intersect operation and named ranges.
You can download the spreadsheet here.
