sorting data into separate worksheets

M

mg_sv_r

I have a worksheet that is a little over 50,000 lines long, it looks
something like this....

A B C D E
F G H I
2116236877 1 GLALHRGLALHRGLA YIDZL1S GB GLA 2 10.50 9149591
4400529033 1 BOMLHRMANLHRBOM VARBMI IN BOM 2 31.85 1439610
1283120077 1 ORDMANLHRMANORD HKXUSB US XIA 2 31.34 0019999

What I need to do is seperate all the data into seperate worksheets
depending on the contents of column I, for example, I need all data where I =
9149591 on one worksheet and all data where I = 1439610 on another.

I could just cut and paste these but with the size of the worksheet its a
bit of a pain. Is there a way to get excel to do this for me?

Thanks in advance for any help.
 
M

Max

Here's a quick-to-set-up pivot table (PT) approach to tinker with
(Steps as in Excel 97, my ver.)

A sample construct is available at:
http://www.savefile.com/files/5348365
Sort data into Separate Sheets_Pivot Table Approach.xls

Insert a new top row. Put in A1: Field1, copy A1 across to I1.
This quickly creates arb. col labels which is required
(The label in I1 should read as: Field9)

Select any cell within the table
Click Data > Pivot Table Report
Click Next > Next

In step 3 of the wizard,
Drag and drop Field9 within the PAGE area

Drag and drop Field9 within the DATA area
It'll appear as "Sum of Field9".
Double-click on it, change it to: Count, under "Summarize by" > OK.
It'll now show as "Count of Field9"

Drag and drop Field1 within the ROW area
Double-click on it, check "None" under SubTotals > OK

Repeat steps for Field1 for Field2, Field3, ... Field8,
placing each successive Field below the previous

Click Finish

The PT will be created in a new sheet to the left.

In the PT sheet,
Click anywhere within the PT,
then click Format > Autoformat > Classic 3? > OK
(quickly applies a nice format to the PT)

Then just right-click on Field9 at the top left corner > choose "Show Pages"
click OK in the dialog. This will quickly create & place each unique item
Click to expand...
in Field9 (i.e. in col I) in its own separate sheet (to the left of the PT
sheet)
 
Top