Data dump excel 1

[kawnz]

Our billing creates a missing/invalid information report in an ansi (txt) format.

I need to turn this into a readable excel form.

The report comes across as follows:

Accession    Patient                         Service      Client    Estimated Price
------------ ------------------------------  -----------  --------  ------------

123L12345    SMITH,JOHN                     09/29/2004   5                20.00
DIAGNOSIS IS REQUIRED FOR ALL COMPONENTS OR THE D.O.S. - MC

123L13652    SMITH,MARY                    04/17/2004   10               30.00
DIAGNOSIS IS REQUIRED FOR ALL COMPONENTS OR THE D.O.S. - MC

123L12654    SMITH,DOROTHY                  04/22/2004   10              100.00
DIAGNOSIS IS REQUIRED FOR ALL COMPONENTS OR THE D.O.S. - MCRR
RESPONSIBLE PARTY REQUIRED -MC
RESPONSIBLE PARTY ADDRESS REQUIRED -MC
MEDICARE HIC NO. REQUIRED - MC
MEDICARE HIC FORMAT INVALID -MC

When I do the text to columns, all the problems are split up also. I need to move the problems over to another columns on the right hand side of the patient information.

Most of the time, there is only one error, but like in the example above, there are some that have multiples.

I need to tell the computer to look for the rows with the patient info, create columns for the errors beneath, stop when there is a blank line, and start again at the next patient.

I hope this makes sense.

Also, every page is prefixed with headers

One Two Three Company   REPORT                    Page    3
DEPARTMENT
  10/06/2004
User: USERNAME                                                         1:45 PM EDT

Accession    Patient                         Service      Client    Estimated Price
------------ ------------------------------  -----------  --------  ------------

That need to be stripped.

Any help you can provide, would be much appreciated.

 

[fredericofonseca]

Obvious answer is to get your billing to output this information on fixed record format with all elements required to identify the Accession.

Other than that and if this is going to be done often (as it seems it is) the option is to create some VB code that loads the file line by line, ignores the empty/page headers/other crap lines, and process the others based on predefined rules.
Something that may be used as a rule is the field with the date. If only one line per Accession can have a date on that particular position then you have a good way of identifying the lines that belong to a particular Accession.

More information is needed, such as
Does the report have footers
If a Accession is split into two pages does the Accession code line get's printed again? (not very important in some cases, but too soon to say on this case).

Also depends on how you need your output.

You won't be able to to this easily and automated just with Excel formulas. VBA is needed for it to work smoothly.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kawnz]

I was able to do accomplish this process manually using standard Excel functions.

First I identified which lines were accessions (by looking for the L as third or fourth character.

Then I copied the accession lines to another sheet.

I then did if-then statements to bring the errors across to the next column(s).


Then I did copy/paste special to get rid of the formulae and have values only.

Then I sorted them, to get rid of the blank lines.

After that I made the spreadsheet look all pretty like my manager wanted it.

Initially, our manager wanted a co-worker to manually key the information from a printed report into a spreadsheet. I was at least able to prevent her from having to do that.

The biggest thing for me was to accomplish the task in the first place. Now that I have at least a way to accomplish the task, I will be looking for ways to automate things a bit more. As you mentioned, this can be difficult based on the 'garbage' that the report contains in the first place.

 

[kawnz]

My many thanks to Frederico for his continued help with the processing of this file.