How to divide a MS Excel Spreadsheet in to multiple files
Posted on 2014-07-22
Hello - I was supposed to set up a business / workflow process and need some advice on what system to use to accomplish this project.
Basically, we are supposed to receive a monthly report from a third party in the form of a MS Excel file, either .xls or xlsx. The workbook should include a single tab with about 10,000 records (about 10 columns), the majority of the columns will include dollar amounts.
Column 1 = Name
Column 2 = Policy Number
Column 3 = Claim Number
Column 4 = Date
Column 5 = $Premium Amount$
Column 6 = $Claim Amount
Column 7 = $Loss Amount$
Column 8 = $Loss Reserve$
Column 9 = $Adjustment Expense$
Column 10 = $Combined Loss and Expense$
I need to take the spreadsheet and divide it into several individual Excel or PDF files with data grouped by one of the columns (Ex, Column 1, Policy Number)
For instance, say there are 10,000 records but 9,000 unique Policy Numbers, I need to generate 9,000 individual Excel or PDF files including grouped data from the spreadsheet and dump each file into a shared directory. So each Policy Number would have their own individual file. We would need to be able to control the way the file name is created/formatted.
We already have a polling type application that will check that shared folder and import the Excel or PDF's into our Document Management program for end user access.
I really don't have any software development experience so I was going try to rely on my experience with either MS Excel or (limited) SQL. I think I might be able to create a macro (vb Script) within excel or ms access but I wasn't sure either program could handle the creation of 9,000 files
Any thoughts or ideas would be greatly appreciated.