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?
|
1 | rockafellerskank 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.
|
2 | RecycledSpinalFluid 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.
|
3 | rockafellerskank 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!
|
4 | RecycledSpinalFluid 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.
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|
|