Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Ideas for sorting multiple worksheets (XL 2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    Is there a better way to sort a list that spans multiple worksheets than using an array, in VBA, and then dump the sorted array into the worksheets?

    I am adding 26 worksheets, one of each letter of the alphabet, and then chop up the list, that is currently in 5 worksheets, into these 26 worksheets and then sort these 26 worksheets, one by one, and then reassemble these 26 workseets into the list's original 5 worksheets.

    But this is very slow, even on a P-4 3GHz machine. I guess adding the worksheets is taking the overhead hit. <img src=/S/sick.gif border=0 alt=sick width=15 height=15>

    Any ideas?

    Thanks a Million <img src=/S/money.gif border=0 alt=money width=17 height=15>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for sorting multiple worksheets (XL 2003)

    Do you have a total of more than 65,536 rows?
    If so, I'd put the list in an Access database and use a query to return the records in sorted order. You can do this using code even if you don't have Access itself. Access handles hundreds of thousands of records with ease.
    If not, I'd put the entire list in one worksheet.

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Ideas for sorting multiple worksheets (XL 2003

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Hans

    Yes I have about 295,300 rows [and growing]. Each of these rows have three alphabetic strings in three cells. I need to sort on the first column and have all the same strings in one block together in the same worksheet starting from A to Z.

    <<< I'd put the list in an Access database >>>

    Can you send me some sort of an official looking document tell my boss that this is the way to go? <img src=/S/drop.gif border=0 alt=drop width=23 height=23> This is what we have been saying all along, but they want Excel and are deathly afraid of anything else. We thought of MS-Word even, and they still say no.

    So we are stuck with Excel. Its <img src=/S/sad.gif border=0 alt=sad width=15 height=15> that Excel is so popular some times it hurts.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ideas for sorting multiple worksheets (XL 2003

    Official looking document? Not really, but you could point to Excel specifications and limits.

    Even if the main list is kept in an Access database, you can still analyze and present the data (in summarized form) in Excel. For example, Excel pivot tables and pivot charts can be based directly on an Access table or query, no need to import the data into Excel. If you set up the queries etc., the end user need hardly know that Access is involved.

    The alternative would be to wait until Excel 2007 is released at the end of this year or early next year. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> The number of available rows and columns will be enormously larger.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ideas for sorting multiple worksheets (XL 2003

    Why not just leave the data on 26 worksheets?
    Legare Coleman

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Ideas for sorting multiple worksheets (XL 2003

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Legare

    Yes this is what I ended up doing. I was re-merging the lists of words, which was not necessary, and was taking a bit too much work and effort. Now I simply leave the words in the 26 worksheets, add new words into these worksheets as they come in.

    Thanks for your reply.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •