How can I split an excel worksheet into multi files based on a name in a column?

Question

I need to split an excel worksheet into multi files based on a name in a column. This sheet has over 4000 rows.. Example

Workbook Name: CO DILE

Sheet name: Report01

Affiliate Account Date Officer Name Amount

XXP 1 1/1/98 Kelly, Ali $255585

XXP 2 1/1/98 Kelly, Ali $256566

XXP 3 1/1/98 Kelly, Ali $478751

XXP 1 1/1/98 Abot, Rachel $255585

XXP 2 1/1/98 Abot, Rachel $256566

XXP 3 1/1/98 Abot, Rachel $478751

New excel workbooks:

File Name: Kelly, Ali CO DILE

Affiliate Account Date Officer Name Amount

XXP 1 1/1/98 Kelly, Ali $255585

XXP 2 1/1/98 Kelly, Ali $256566

XXP 3 1/1/98 Kelly, Ali $478751

New excel workbooks:

File Name: Abot, Rachel CO DILE

XXP 1 1/1/98 Abot, Rachel $255585

XXP 2 1/1/98 Abot, Rachel $256566

XXP 3 1/1/98 Abot, Rachel $478751

Please please help!!!

Answer 1

I think you are going to need to export your data to text and use a scripting language to read the text and automate the creation of files. I would do this like so:

• Export your workbook to a CSV file
• Write an ASP page / VB script / Another technology that takes each line of text from you CSV file and parses it, creating new CSV files as it goes
• Copy and paste the text from the CSV file into the ASP page
• The CSV files can then be opened in Excel or maybe there's a bulk CSV to Excel conversion tool?

Hope you find this answer useful.

Answer 2

If you don't have a prohibitively large number of files to create, you could do this manually by filtering the original data by Name and then copying the matches into a new workbook. I would say if you have 25 or fewer files to create, this might make more sense than taking the time to wrestle with the VBA.