Wrapping sheets within a workbook

[OfficeManager]

I am working with raw data in Excel 2003 in a workbook where there are 3-5
sheets completely filled with data, all 65536 rows. There are duplicates
scattered throughout the sheets. Is there a way I can link all the sheets
together so that when I do a sort, it will sort all the rows on each sheet
within the workbook?

 

[Pete_UK]

You would need a macro to do that. This is an example of a Merge Sort
algorithm - you would need to sort each individual sheet, and then you
could look at the topmost item in each sheet and decide which record
should be written to a new sheet (and adjust a counter for the sheet
where that record lies). Then keep repeating this, looking at the
topmost item (as defined by the counters in each sheet), writing the
record to a new sheet and adjusting the appropriate counter. Another
check needs to be made to see how many records have been written to
the new sheet, so that a further new sheet can be used after 65536
records. It would be possible to remove duplicates as part of this
process.

You would end up with 3 - 5 sheets, where the first might cover A-E,
then F-L, then M-R etc.

I don't recall seeing examples of a merge sort algorithm being coded
on any of the usual web-sites.

Hope this helps.

Pete

 

[Otto Moehrbach]

As Pete said, you would need a good bit of code (programming) to do it all.
One suggestion I would give you is to find someone/computer that has Excel
2007. 2007 has over a million rows and could handle what you have easily by
putting everything on one sheet and then sorting that one sheet. It
wouldn't be a lot of work to then copy 3-5 batches of 65536 rows and paste
them into 3-5 sheets. Then delete the one sheet that has more than 65536
rows of data and save the file as a 2003 file. HTH Otto