Excel Data Extract

Monthly,I download, from a third-party vendor, a detailed accounts receivable report for our company that consists of roughly 1000 patient names, insurance company, amount they own, charge number, etc. When downloaded, this data is automatically exported to Excel with no other options. I've asked for other formats like XML, CSV by patient, but no luck. I have to reformat this report monthly to a summary report, which take about 12 hours for all clients. I've been playing with vlookup, hlookup, and now macros but I'm running into road blocks. I've attached two images. Image Excel Report 1 depicts the report in it's native view, full detail. The second image is Excel Report 2, is how I need to format the report. I started writing a macro with "find, offsets, copy and paste" but I'm very new at this and was looking for some guidance. I also attached an Excel spreadsheet with the same data..

Any help is appreciated as I don't want to go down the wrong road.

Report from a large data system that is not at all in the format the user wants? Nah... never heard of that before.
I think VBA is the way to go here.
It goes through the cells in column F until it finds a cell containing the string "Payer Name:". It then uses that as a base and grabs the other values by going 2 steps down, one step back etc. The only trouble maker I spotted was the Chart since it seems to exist only once for each patient but I think I fixed it. Also the Charge ID is a bit odd since the value is not in a cell of it's own. I separated it out in the list.
The headers are copied from a hidden sheet.
Test, test and test. Then come back with a bug report.
What amount of data are we talking about here by the way? 100 lines? 1000 lines?

Whada ya know! It wasn't that bad with formulas after all. Test, test etc.

Edit: removed macro since, only formulas now

Is the report at least in a standard format?

name
3 rows
charge ID
if 2nd charge
8 rows
charge id

Hi erickers,

Welcome to the Forum!

I wrote a routine for you - it needs the data sheet active at the start

Please Login or Register  to view this content.

You're welcome!

By all means, let us know

xladept, I think you fell for the trap with chart that I mentioned in my post. The result in cell C3 is wrong I'm afraid.
Interesting coding style, what programming language is your "native" one?

My own misstake was to completely miss the charge ID, he he...
Here is a working version. I also changed to using the Find function instead of looping through cells, like xladept.

Please Login or Register  to view this content.

Thank you JACC. I may found another issue that could change things. Where charge ID is unique for each patients charge there can be multiple CPT Codes (multiple charges as in the attached example there are 2 but there can be 4 or more) under one charge ID. I didn't notice that earlier. The spreadsheet that is downloaded monthly is usually about 1000 patients or 11500 rows in the sheet. See attached as an example for multiple CPT codes and charges.

The report is in standard format at least for now. That is the caveat to all of this as if it changes it could break the results but I can always make the necessary changes to the code to reflect any offsets.

I'll fix it, standby.

Code now looks like this. Take your time and test.
For some reason I decided to use Billing Provider as a base this time. I don't know which is more robust. Can the value under Billing Provider sometimes be missing even if there are CPT and the other data on that row?

Please Login or Register  to view this content.

Hmm... That many rows. Well let me know how it works and how long it takes. I can disable screen update when it runs, it will make it run twice as fast or something like that.

Hi Jacc,

xladept, I think you fell for the trap with chart that I mentioned in my post. The result in cell C3 is wrong I'm afraid.
Interesting coding style, what programming language is your "native" one?

Yeah - I missed that, but had noticed it earlier, - I hadn't read your post

My native style?

I first learned AUTOCODER at IBM school in 1965.

I learned and wrote FCOBOL on the job and maintained ASSEMBLER code.

I learned FORTRAN, BASIC and ASSEMBLER in Graduate School.

I taught BASIC in a Community College.

I taught myself VBA over the last 20+ years.

So, I guess my style is a hodgepodge and still evolving

BTW - I've learned an awful lot while a member of this Forum

@ erickers - here's a correction for the Chart and CPT issues - although it looks like Jacc has taken good care of you:

Please Login or Register  to view this content.

Jacc, so we're real close, I think. I ran the code against the complete list and I noticed some issues but not with your code, it was how the report is formatted. I noticed in some instances that "Payer Name" in column F was missing and "Created Date" is now in that field. However, if you notice in column A there is also the payer name, which I'm told is the one I need to use because the "Payer Name" field in column F isn't always the same as the payer name in Column A and as mentioned is sometimes missing and replaced with the field "Created Date". I also checked Billing Provider and that is constant thru the whole list. I added your spreadsheet back as an attachment with more records and an example of the "payer name" issue in column F. When Payer Name gets removed "Charge ID" and "Chart #" moves down a row. It appears the whole row just gets removed. It becomes an offset issue which may be now a bigger problem. What a cluster. I apologize for these changes.

I decided that patching up the old code with IF's would not look good so I redid it. Ofcourse, all the bugs in the world decided to have a convention here now so it took a while.

Now it's just scanning for either something in column A or Billing Provider in column C.

Please Login or Register  to view this content.

That's quite a resumé you got there, xladept. I guess VBA is a loong way from those low level languages.

That's a really good question.
Code now looks like this.
I see you are catching up fast now, xladept. I tried your code but only after a little work with tab and enter.

Please Login or Register  to view this content.

I'm glad I redid the code before, it's much easier to fix stuff like this now.

He he... I'm not paying attention as usual.
I'm not completely satisfied with this since things will get messed up if either of the Payer name, Chart or Charge ID occur on the same row. It doesn't seem like that would occur though.

Hi Jacc,

I had pretty much abandoned this thread to you - I'll keep in touch, but now, I'm logging off for the day - what about your resume

erickers, you have to provide more sample data for me to test with, version 9 works perfectly fine with the data provided as far as I can see.
My resumé? He he... it's best left unwritten. Programming wise I have one and a half college class of TurboPascal but I never used it. College also forced me to teach myself Matlab, played with that for a while. A few hours of college assembler for microcontrollers, never used it.The VBA is self taught of course, using it from time to time at work.

Oh there can be several charts for each payer, I see. I'll fix it.

Code actually looks even more tidy now, it's usually a good sign. The whole thing feels pretty robust now. May the testing commence!

Just did some clean up in the code, results are the same.

Please Login or Register  to view this content.

Glad to hear it works! I assume you are using version 10.5, it's a tiny bit better.
The basecell has always been a range (the term "range" in VBA can be one cell or many cells). There is a lot less Basecell going on then in the early versions which means that it is much more flexible and robust. As long as the columns don't change it will take any shifting position of any of the three Payer name, Chart and Charge ID (within reason). The rest of the data is picked using offset from the cell containing billing provider (the basecell). That could be changed to some other cell if you believe that would be more robust. The three IF's in the end checks if any of the first three has not been filled out (chart can be missing for instance) and if so just copies from the row above in the report.
I did stumble upon one thing that had me worried, the cell two rows above the payer name in column A often contains a single space. However VBA still regards that as an empty cell so no problem.

How long does it take to run on a full sized report?

Hi Guys,

I've been playing around with this also - this one keys on the service dates and uses Worksheet Arrays - if all is now known, this should be fast

Please Login or Register  to view this content.

Hi again xladept. I ran your code and it produced different results than mine at C4:C6 and C13.
I must admit, I find your code a bit hard to read with the multiple commands on one line separated by :. I assume that style was common for one of your many previous languages but which one? However, the one liner IF's I liked so much I implemented them in my code.
My curiosity made me do a little work with tab and enter, I guess that makes the code harder to read for you?
When selecting multiple lines and using Tab and Shift + Tab to move blocks of code back and forth I discovered that the VBA editor recognizes your labels and let them stay at the very left, pretty cool.
Do your recognize this piece of code?

Please Login or Register  to view this content.

Hi Jacc,

I acquired the habit of condensing my code so that it would fit on one page of my book - so, the style was evolved

Please Login or Register  to view this content.

It's not any harder to read

The conversion to worksheet arrays was the subject of my entry in the Tips & Tutorials.

'''''''''''''''