That’s the stuff I did in Excel at the pharmaceutical company.
That one project – MY god the data was a mess. CD ROMs, stuff off IBM mainframes, stuff in emails, surveys from who knows where…. oh and website data too.
Pulling it all together in a way that was “pushbutton” was where I ended up where it gathered all the data in whatever state it was at. That’s where some of the VBA came in really handy, cleaning up the files prior to bringing them into the ginormous spreadsheet that did everything.
2000+ distinct reports which were applications of their own, auto emailed to 8000 sales reps (4 sales reps per territory as they used a district/territory model). which gave them a UI where they could create their own graphs and charts as needed … on a weekly basis. All using Excel and VBA [I made a “configuration screen” for that stuff).
It was for Claritin / Zytec / Allegra comparison at first, and then for others, and it drilled down to the Doctor / insurance company /drug most commonly prescribed / patient level.
Felt wrong even having all this information tbh.
But with it they each knew what the doctor was prescribing and which insurance companies would pay for Claritin and which ones would not.
That was one big major project among others. It generated the company $$$, which I’d occasionally hear “how much per district” and it felt good to help.
They hired a $2 million team from Microstrategy to replace what I did which is a whole story in itself as it was sneaky, and it failed ultimately. But when I left, I trained 3 people on it, I moved to Florida and they were still modifying it for new drugs and theywere STILL using it 8 years later in 2010.
As Schering merged with Merck somewhere along the line I have no idea if it’s still running in 2020. I sure hope not. That’s a horrifying thought.
More horrifying is that I knew in the early 2000s just how much pharmaceutical companies were ripping off which countries. I had so many non-disclosure agreements to sign when I worked on that stuff.