Home / Data / Using a macro to help me review data

Using a macro to help me review data

New Idea - Using Macro as Guide to Review Data

[Yesterday I was going to do a post but Microsoft had other ideas for me. It turned out they hadn't finished updating my machine last week so last night I had to let it finish the update. Besides WordPress was telling me that I did not have the latest version of Microsoft Edge. Unfortunately, by the time Microsoft finished the update, it was time for me to wrap up and get ready for bed.]

Lately, I've been trying to figure out how to review data for the ACA. Last year, the data format I received had changed so I had to quickly fix some formulas but the fix was very roughly so there were some errors. I had to manually fix those errors. The largest errors were the totals or 12 months column for 1H, 1E identification. In addition to fixing those formulas, I also decided to streamline the logic of the formulas so that they were a bit easier to follow. I think I successfully tightened the logic.

Now I'm trying to clean up the instructions and figure out some kind of guidelines on how to review the data for any inconsistencies or illogical time frames (end dates before start dates - for example, not that this type of errors happened). This is a something different and I don't think many people have tried designing a program to identify how to review data for a specific problem. 

Here's my approach: I have a tab called "Checklist" where I list all of the conditions to filter (as examples: working all of last year, not working last year at all, or being hired in the middle of the year and being still active at end of year). I don't know how many conditions I will have but it will be a list of them. The macro will go down this list and see if the condition has been marked as reviewed. If it has, the macro will skip and move on to the next condition. The macro will continue to go down this list until it encounters the first unchecked condition, at which point the macro will mark it as reviewed and then apply the proper filters.

Then the macro will stop to allow me to review the ACA codes to see if they make sense and if they are being applied correctly. The macro will tell me what condition it has filtered for and it also gives me instructions on what to look for and what to do. By setting up a certain condition (for example, did not work at all during the year), the filtering makes it easier to review the codes - every row should have the same set of codes to meet that condition. Once I am satisfied with the ACA codes for that particular condition and have made any necessary changes or have marked as good to print, I start up the macro again to go to the next unmarked condition on the checklist. 

I envision that when the macro has gone through all of the conditions (this will be done by pushing the macro button multiple times in order to go down the checklist), it will then inform me that I have gone through all of the conditions. The macro will clear the marks so that the next push of the macro button, the macro will start all over again on the checklist.

There may be changes to the idea but this is my initial approach to reviewing the data. I might even add some program to do a certain kind of review itself (rather than me), but this is for later. First, I need to get through the checklist. 

I am not aware of anyone doing this kind of review check but that doesn't mean no one has done this. I just think it may be rare, so this could be something new.

Anyway, we'll see how this goes.