Hey guys, i’ve lurked on here forever now and have learned so much. Always considered myself a resourceful guy (been writing my own scripts for games since I was 12) however my actual education is limited. I graduated from college in accounting / finance, not software or IT.
However i’m known as the go-to guy for automation and solutions at my job. I created a basic script that automatically enters medical claims for us, for example.
The current issue is this. We print invoices for around 800 groups right now every month, half on the 10th and half on the 20th. The way we’ve done it for the past 25 years is printing them out, sorting them out by hand into which ones have a previous balance, current balance or a credit balance
I quasi-automated this process over the last week with mixed success.
I converted these PDF invoices that we pull from our database into a huge excel spreadsheet, then I wrote a VBS script to insert page breaks after each invoice, today we had 405 of them - effectively making the excel workbook 405 pages. Then i conditionally formatted the words “previous balance” and “credit amount”, highlighted them pink and blue. Then i filtered the invoices by those colors, found out which page #s they were on, made a separate excel sheet, typed out the irregular page #s, and printed out the invoices by hand, excluding the irregular ones. then i went back and printed only the irregular ones, therefore eliminating the need to sort them by hand.
My question is, is there a better way to do this? For example, if invoices #202, 209, 308 ,315, 398, etc. had previous balances, is there a way I could create a range so i could easily print these from the PDF file? i had to type manually print pages 1-30, 32-34, 36-45, etc. leaving out the previous balances and credits, then go back through and print the irregulars.
PROBLEM 2, when we bill our fund office for our services, we list each group we administer benefits for. This is an enormous spreadsheet, and it lists how many active members there are for each division
for example, it would look like this
DIV ACTIVES
120 45
121 32
122 82
etc. What we do when we bill each month, is pull a PDF invoice that’s 1200+ pages long wth a division per page, print it out and manually type in each and every single actives number in a new column. like this
DIV ACTIVES MARCH ACTIVES
120 45 44
121 32 32
122 82 105
this automatically tells us in the next column how many members each division lost or gained, to know how much $ we should bill the fund (we profit $3 per member per month)
My question is, is there a way to convert this huge odd file to an excel spreadsheet, pull the # of active members for each division, and paste them right next to the previous months? This process takes about 6-7 hours per month to do and it’s extremely pain staking and mind numbing.
Thank you guys so much in advance, I can’t wait to continue my education here.
However i’m known as the go-to guy for automation and solutions at my job. I created a basic script that automatically enters medical claims for us, for example.
The current issue is this. We print invoices for around 800 groups right now every month, half on the 10th and half on the 20th. The way we’ve done it for the past 25 years is printing them out, sorting them out by hand into which ones have a previous balance, current balance or a credit balance
I quasi-automated this process over the last week with mixed success.
I converted these PDF invoices that we pull from our database into a huge excel spreadsheet, then I wrote a VBS script to insert page breaks after each invoice, today we had 405 of them - effectively making the excel workbook 405 pages. Then i conditionally formatted the words “previous balance” and “credit amount”, highlighted them pink and blue. Then i filtered the invoices by those colors, found out which page #s they were on, made a separate excel sheet, typed out the irregular page #s, and printed out the invoices by hand, excluding the irregular ones. then i went back and printed only the irregular ones, therefore eliminating the need to sort them by hand.
My question is, is there a better way to do this? For example, if invoices #202, 209, 308 ,315, 398, etc. had previous balances, is there a way I could create a range so i could easily print these from the PDF file? i had to type manually print pages 1-30, 32-34, 36-45, etc. leaving out the previous balances and credits, then go back through and print the irregulars.
PROBLEM 2, when we bill our fund office for our services, we list each group we administer benefits for. This is an enormous spreadsheet, and it lists how many active members there are for each division
for example, it would look like this
DIV ACTIVES
120 45
121 32
122 82
etc. What we do when we bill each month, is pull a PDF invoice that’s 1200+ pages long wth a division per page, print it out and manually type in each and every single actives number in a new column. like this
DIV ACTIVES MARCH ACTIVES
120 45 44
121 32 32
122 82 105
this automatically tells us in the next column how many members each division lost or gained, to know how much $ we should bill the fund (we profit $3 per member per month)
My question is, is there a way to convert this huge odd file to an excel spreadsheet, pull the # of active members for each division, and paste them right next to the previous months? This process takes about 6-7 hours per month to do and it’s extremely pain staking and mind numbing.
Thank you guys so much in advance, I can’t wait to continue my education here.
