Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

When a test is cancelled in the system it appears as a charge and then a credit with the same date and time.

The billing team does not want to see these, as they have to charge for a test and them immediately credit a test, and it’s a hassle.  So we clean them up before submitting.
Often a test gets ordered, cancelled, and then re-ordered, so it is important to make sure the code doesn’t delete the re-ordered test:

Read More Read More

Excel Macro Tutorial 4 – Delete non-chargeable tests and sort.

Excel Macro Tutorial 4 – Delete non-chargeable tests and sort.

The billing report has a bunch of tests with at cpt code of ‘0’ or a blank.  These are non-chargeable tests.  We used to have to delete them manually.
bill report 0cpt
For this we can loop through the rows and delete non-chargeables just like we did with the junk removal.  For efficiency we could just add this code to the junk-removal macro, but I have kept them separate here for the sake of simplicity. I called the macro ‘DeleteZero’ and copied and modified from the last macro:

Read More Read More

Excel Macro Tutorial 3 – Remove header rows and delete junk loop

Excel Macro Tutorial 3 – Remove header rows and delete junk loop

Ok now we’re getting into a bit more advanced material.  We’re going to have the macro do some real grunt-work for us.  Here’s what we want it to do:
1. Find the last row of the report.
2. Go through the entire report, row by row, and delete anything that we don’t want.

The report has a bunch of junk we need to delete:

Simple enough, the only problem is you can’t just record yourself going through the report deleting stuff and then go back and change the code. 

Read More Read More

Excel Macro Tutorial 1 – Open a text file

Excel Macro Tutorial 1 – Open a text file

In this 6-part series of tutorials, I show you how I was able to automate a process that took 5-10 minutes per report.  Now the process takes about 30 seconds.  The techniques and code examples from these tutorials can be adapted and used by anyone who has the misfortune of being delegated to do tedious gruntwork in Excel.  Many concepts like ‘how to record a macro in Excel’ are NOT covered in this tutorial.  There are plenty of tutorials on the web covering the basics of Excel macros.  Instead this tutorial fills in the gaps by explaining a real-world example from start to finish.

How to open a text file with an Excel Macro:

Read More Read More

Lab Jokes – You know your instrument is a piece of junk when…

Lab Jokes – You know your instrument is a piece of junk when…

You Know your instrument is a piece of junk when…

1. You know the service guy on a first-name basis and have even considered inviting him over for beers sometime.

2. You know the serial number of your instrument by heart from reciting it to tech support so many times.

3. You have actually physically assaulted the instrument while no one was looking.

4. Everyone knows you physically assault the instrument but they never tell anyone about it.

Read More Read More

SynapSonic Software

SynapSonic Software

SynapSonic Software produces software for medical laboratory and general use:

Along with commercial software I have also written a few other programs.

Download these Free, useful SynapSonic utilities here! database_down


Ol Monitor Ol Monitor is a turn-around-time management and acceleration tool for labs using the Sunquest lab information system.  OL_Monitor is a program that interacts with the Sunquest™ (Formerly Misys™) Laboratory information system. OL_Monitor integrates seamlessly with Esker™ Smarterm, the client of choice for most laboratories. Like a tireless unblinking eye, it keeps up with the Outstanding log. In addition it adds critical information the Outstanding log lacks, how long tests have been outstanding. This is just the beginning, read on to find out.

Lab Jokes – Q&A

Lab Jokes – Q&A

Medical Lab Jokes – Q&A

Q: What type of red cell will be observed on the peripheral smear if blood was refrigerated prior to making the smear?
A: A brrrr cell.  -Credit: (I’ve known this one so long I may have made it up myself.)

Medical Lab Jokes – You know you’ve been in the lab too long when..

Medical Lab Jokes – You know you’ve been in the lab too long when..

You know you’ve been in the lab too long when:

You wonder what absolute alcohol tastes like with orange juice
You can’t watch CSI without cursing at least one scientific inaccuracy
You use acronyms for everything and never stop to elaborate
You always seem to use the microscope after the person with the impossibly close together eyes

Read More Read More