RotoGuru Computer Forum

View the Forum Registry

XML Get RSS Feed for this thread


Self-edit this thread


0 Subject: Excel Macro needed

Posted by: rockafellerskank - Dude [27652109] Thu, May 08, 2008, 19:37

I have an ugly set of spreadsheets that I have to manipulate some data out of so I can analyze the remaining data. Unfortunately, the spreadsheet formatting is such that there is very little in common that provides a positive trigger to tell me which line to delete.

Doing this manually isn't feasible, so I'm hoping a macro will work.

in Column A there is a bunch (multiple lines but not always the same # of lines) header info such as patient name, insurance company, acct#, etc....
At the end of this 3,4,5 lines of header is a line with the text **PATIENT SUMMARY:

To the right of that is a summary of payments and adjustments.

Now, here's the problem. There are 3 insurance companies that I want to eliminate the the **PATIENT SUMMARY: line as those totals need to be excluded.

If the header contains "Mercy Care" or "Shaller Anderson" or "Childrens Rehab" I want to eliminate the very next **PATIENT SUMMARY: line.

So, Can a macro be designed to start from A1, A2, A3... and when it discovers on of those 3 key words above.. proceed to A4, A5, A6.... and delete the **PATIENT SUMMARY: line? Then of course it would need to reset into "search for phrase mode" until t finds a key word again, then delete, and recycle, recycle, etc...

So, the search might go like this....

A1 - no key word
A2 - no key word
A3 - no key word
.
.
.
.

.
A99 - found key word "Mercy Care"
so proceed to
A100 - didn't find **PATIENT SUMMARY:
A101 - didn't find **PATIENT SUMMARY:
A102 - didn't find **PATIENT SUMMARY:
A103 - didn't find **PATIENT SUMMARY:
A104 - found **PATIENT SUMMARY:
so delete line A104
read line A105 - no key word
A106 - no key word
A107 - no key word

.
.

.
.
.

.
A121 - found key word "Shaller"
so proceed to
A122 - didn't find **PATIENT SUMMARY:
A123 - didn't find **PATIENT SUMMARY:
A124 - found **PATIENT SUMMARY:
so delete line A124

(and reset and continue and so on)


When the entire Macro has run to the bottom of Excel A65xxx or whatever. Stop.

I shoudl be left with a speadsheet that has only **PATIENT SUMMARY: lines whose header is NOT of any of those 3 key phases above. I can then sort/auto-filter by **PATIENT SUMMARY: and add up the debits/credit associated with those "good" data lines.

Easy, right?




1rockafellerskank
Dude
ID: 27652109
Fri, May 09, 2008, 01:09
Thanks RSF. e-mail sent

By the way, the version I sent is XL 2003. I have access to XL 2007 if it makes a difference.
2RecycledSpinalFluid
Dude
ID: 204401122
Fri, May 09, 2008, 03:00

Const ForReading = 1
Const ForWriting = 2

arrSearchText = Array("Mercy Care", "Shaller Anderson", "Childrens Rehab")

Set objArgs = WScript.Arguments
If objArgs.Count = 1 Then
strInputFileName = trim(objArgs(0))
Else
'No DragDrop
Set objDialog = CreateObject("SAFRCFileDlg.FileOpen")
x = objDialog.OpenFileOpenDlg
If x Then
strInputFileName = objDialog.FileName
Else
WScript.Echo "No File Specified." & vbNewLine & vbNewLine& "Exiting Application."
WScript.Quit
End If
Set objDialog = Nothing
End If

strBackupCSVFileName = Left(strInputFileName, InStrRev(strInputFileName,".") - 1) & ".csv"
strOutputFileName = Left(strInputFileName, InStrRev(strInputFileName,".") - 1) & "_mod.csv"


'Do a Save as CSV of original
Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
'objExcel.Visible = True

Set objWorkBook = objExcel.Workbooks.Open( strInputFileName )
objWorkBook.SaveAs strBackupCSVFileName, 6
objWorkBook.Close True
Set objWorkBook = Nothing
Set objExcel = Nothing



'Now parse out stuff
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objReadFile = objFSO.OpenTextFile(strBackupCSVFileName, ForReading)
Set objWriteFile = objFSO.CreateTextFile(strOutputFileName, ForWriting)

booCheckForTrigger = False
Do Until objReadFile.AtEndOfStream
strValue = objReadFile.ReadLine
If booCheckForTrigger Then
If InStr(strValue, "**PATIENT SUMMARY") = 0 Then
objWriteFile.WriteLine strValue
Else
booCheckForTrigger = False
End If
Else
For intLoop = LBound(arrSearchText) To UBound(arrSearchText)
If InStr(strValue, arrSearchText(intLoop)) > 0 Then
booCheckForTrigger = True
End If
Next
objWriteFile.WriteLine strValue
End If
Loop

objReadFile.Close
objWriteFile.Close

Save that as "remove_stuff.vbs", and either drag/drop the CSV on it or double click it and browse to the file.

Not as graceful as I'd like, but what it does it clone your XLS to a CSV then parse out the lines with the "arrSearchText" text in them, then does a save as to "_mod.csv".

I think it could be reworked to only deal with XLSes, but my brain is slowing down tonight.
3rockafellerskank
Dude
ID: 27652109
Fri, May 09, 2008, 11:31
I wanted to give RSF some public props. I'm never ceased to be amazed at the helpfulness and resourcefulnesses of this community.

As my way of sating thanks to RSF, I will be sending a donation to Guru in his name!

4RecycledSpinalFluid
Dude
ID: 204401122
Fri, May 09, 2008, 12:01
Email me the amount and I'll match it. Been a while since I dropped some coin to Guru.
RotoGuru Computer Forum

XML Get RSS Feed for this thread




Post a reply to this message: (But first, how about checking out this sponsor?)

Name:
Email:
Message:
Click here to create and insert a link
Ignore line feeds? no (typical)   yes (for HTML table input)


Viewing statistics for this thread
Period# Views# Users
Last hour11
Last 24 hours11
Last 7 days11
Last 30 days77
Since Mar 1, 20071177583