I am looking for a skilled freelancer who can customize a macro in an XCEL spreadsheet for data manipulation. The macro should be set up to run automatically. The project requires a moderately complex level of expertise.
Ideal Skills and Experience:
- Proficiency in XCEL and macro customization
- Strong understanding of data manipulation techniques
- Ability to set up macros to run automatically
- Experience with moderately complex macro customization projects
Every month I receive a spreadsheet detailing the accounts that have been paid by insurance since receiving the prior spreadsheet. I work with several different surgeons.
The goal is to manage the data to create invoices to send to the surgeons.
[login to view URL] are several columns from the report that have unnecessary information and I delete them. They might be necessary to the search query of my billing company, but I don't need it for my invoices. It doesn't need to be deleted if you can isolate the other columns as follows.
2. From the remaining data, I separate the individual doctors, creating a new workbook page for each doctor, which contains only their data. There are about 7 doctors, so now there are 7 wb pages and I title the wb page tab with the Doctors name.
3. Then, on each wb page, I sum the amount insurance has paid. I then subtract that amount from 500x the number of cases. (I am paid 500 per case, so if insurance pays 250, the doctor pays 250.) This is the amount that is billed.
4. Now I break down each wb page total by the location where each case was completed (some of the docs in the group work at multiple locations) so I sum the amount from each location then subtract it from 500x number of cases at that location. The sum of all the locations should total the doctor's balance for that wb page.
There are a few doctors that work infrequently, so they may be on the spreadsheet one month and not the next, or I could add a new doc, so if there is a way to create the macro so these possibilities are addressed would be ideal.
I want all this to be automated through a macro or a couple macros, so I can select the macro and all of the above happens automatically.