Situational Case Studies

MREXCEL IS THE LAST WORD ON EXCEL SOLUTIONS

— A sales department needs to import customer sales data. They need to break it out by region and mail each regional department head just the records for that particular region. This is a time-consuming manual process that can be automated using custom Excel VBA code.

— A major utility company monitors wind speed at hundreds of wind turbines every 10 minutes. They need to calculate how many hours per month the turbines were experiencing sufficient wind and compare this data to a separate list of fault times to figure out what percentage of the available windy days were lost due to equipment failure. With over 2 million data points per month, this process would take days to calculate manually. Using a custom VBA macro from MrExcel, they were able to have the report in hours.

— Buyers for a chain of retail stores need to be able to track stock and sales data for the 42 stores in the chain. Using a custom program from MrExcel, they can import mainframe data daily and see in a glance the stock status of the top 10 selling items in each category. Inventory can be easily re-ordered using replenishment logic. Pictures can be displayed of the top selling items.

— Stock day-traders can collect real-time stock market data using various tools. However, with new quotes streaming in every second, it is hard to make sense of the data. MrExcel has written several custom programs to track the incoming quotes and spot trends to alert the stock trader of early movement in a stock.

— A marketing company buys data of recent mortgage recordings from the county government. This electronic listing contains the necessary information, but in the wrong format for the mailing software. MrExcel wrote a utility to import the government's file, extract and reformat the information in the format needed for the mail merge program.

Case Study: WebSitesAndMore.com

Jennifer Holley owns WebSitesAndMore.com, a hosting and design company in Vermont. Working on a large project for a corporate client—creating 380 branded websites from one custom designed template system—she needed Bill’s help to manage website changes. Every time she made changes to a Master Excel File, she had to change her master file. Every one of the 380 individual worksheets had custom information that had to be re-saved in a manner that would bypass file formula and retain customized information.

Holley was spending 35 minutes saving all 380 sheets every time a piece of information changed, taking time away from her busy practice. Holley contacted "Mr. Excel," Bill Jelen, who is a world authority on Microsoft Excel, its uses and how to milk its hidden capabilities for custom application. She had heard of Jelen's reputation as the “Mr. Excel," proprietor of the well-established MrExcel.com website, renowned for its Excel-based software applications and solutions. After reading that Jelen has saved companies millions of dollars and many man hours generating reports, Holley was ready to pay anything if he could solve her having to routinely manually correct hundreds of files.

Jelen created a Macro that saved all 380 sheets as a .csv (comma separated variable) file whenever any piece of data was changed. Holley estimates Jelen's ingenuity saved her at least 10 hours of work each month and much frustration.

"I have master databases that are intense and long, and when I change one thing they all change. Bill's solution gave me the ability to make changes in a few seconds rather than a half an hour. I asked if he could do the same thing and save them as a text by changing one line of code. He did that, and now, I can apply that macro to the same project as a text file," Holley said. I thought I was giving him an impossible task, but he came through - He truly is – Mr. Excel!"