Well, sad to say this one has stumped me for a long time. I work in Numbers from Apple’s iWorks Suite about as much as I do in Excel. Data sets above about 1000 rows, assuming use of processor-heavy formulas like vlookups, really slow Numbers down. From a visual and formula perspective, Numbers is much easier to use. One of my favorite aspects is the ability to create header rows so that the last row becomes the column headers and the references in formulas. That makes modifying data quicker and improves one’s ability to keep track of a greater variety of segments. Continue reading “Excel Tidbits: Creating Column Header Rows for Sorting”
I often mash and extract data using Excel. I needed a way to pull out the domain from a web address and compare it with domains from other sheets and activities. This then gives me insight into how often we do work on websites across all our projects.
Version Note: The formula below only works in versions of Excel that can read the xlsx format. Older versions of Excel (pre-2004) are limited to seven nested formulas. This equation has eight. The iserror formulas in the if statements checks for errors and displays whatever is in the A6 cell if an error occurs. If the cell is blank, it should stay blank. I simply used the blank check upfront to make the formula more efficient in processing. Continue reading “Excel Tidbits: Extracting Domains from URLs Function”