Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!!!

share|improve this question
2  
Sure hope you made up your data for this example. You'll need VBA to solve your problem from within Excel, so you'll have to dive into that. – Lance Roberts Sep 29 '11 at 18:18

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.

share|improve this answer

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .