Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.