How can I split a giant excel spreadsheet into smaller worksheets?

Factual Questions
1

Ok I got a situation here and need an expert to share their expertise.

I have a giant table of data in MS Excel, looks like:

Name | Status | email | Office Code

It is very long. I need to divide this list up, by Office Code, into separate worksheets (so that each Office Code, which contains maybe 20 people, has its own separate worksheet)

Is there some way to Automatically do this?

2

I don’t know your skill level but I would start by sorting the giant table by office code.

Then depending on how many office codes you have you could copy each section to a separate sheet.

No automatic function will do this for you. It can be done somewhat automatically by writing VBA macros but if you aren’t familiar with that it might not be worth the effort.

3

One option that is a little easier but still has a lot of manual steps is to use a Pivot table.

  1. Make sure your data has one field that will identify the group. In your example it looks like office code will work. Add a field if necessary.

  2. Select the entire data range and create a pivot table from it with the identifying field as a row label and any other field in the values section. That would be office code for the row and name for the values.

  3. If it doesn’t default to counting the values in name, set the “summarize by” to count.

  4. You will then get a list of office codes with a count of the number of people in each.

  5. Double clicke on the number for an office code. Excel will extract all of the rows supporting that value into a separate tab. Rename the tab to something that matches/relates to the office code.

  6. If you need the data in separate files, move the tab to a new sheet.

I glossed over some steps there but that is the basic approach.

4

If I had to do it, I think the fastest way is, for each office code:
(1) Take the original spreadsheet;
(2) Sort by office code;
(3) Delete all the lines except for those with one office code;
(4) Save the spreadsheet WITH A NEW NAME, e.g., with a name including the office code;
(5) Close the spreadsheet.

Then repeat those steps for each code.

5

Jeez. I just spent all day today doing this very thing.

I had a CSV file that was almost 5 million records (1.5 gig). I downloaded an app called BatchIt! (link not included because I don’t wanna be busted for spamming). This app broke it up into manageable pieces that could be imported into SQL using a custom tool that processed records at a blazing 20 records per second. I didn’t write it.

You might ask, “why didn’t you use SQL Import wizard to do this”?

So did I.

Export your excel file to a CSV, run the BatchIt, then re-import into Excel, each in a new sheet.