Results 1 to 8 of 8
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Disjointed sort (Excel2003)

    I would like to automate a 'disjointed' sort.

    Suppose I have a single column data block (BlockA) of codes entered as text in [A1:A20]

    Suppose I have another block (BlockB) containing multiple columns but same number of rows e.g. [DA1M20]
    The cells in Block B are all formulas, returning a numeric percentage value.

    I want to be able to sort say, only the single column range [DH1H20] within BlockB, in ascending value order, but I want the corresponding row entries in BlockA to be 'included' in the sort, i.e. synchronised.
    Similarly, I may want to sort only the column range [DP1P20] within BlockB, again with the corresponding entries in BlockA 'moving' with this sort.

    For various reasons, I cannot simply define a 'new' single sort range block which includes both e.g. sorting on range [A1H20]

    Any suggestions???

    zeddy

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

    Re: Disjointed sort (Excel2003)

    Move the column from block B you want to sort on so that it is adjacent to block A, for example by dragging it with the Shift key held down.
    Select the two now adjacent columns and sort on the second one (the one you moved.)
    Move the column back to its original location.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disjointed sort (Excel2003)

    Cut and paste the data of A1:A20 into DN1. In A1, type a formula : = DN1 and enter. Autofill this down to A20. Then sort on any column in Block B. (Since the data in A1:A20 is a copy of the Block B column DN it will reflect the changes that occur as Block B is sorted).
    Regards,
    Rudi

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Disjointed sort (Excel2003)

    Nice idea!
    However, I don't have a spare adjacent column.
    (I suppose I could temporarily insert a column and then remove it - but it is a complex worksheet structure and I'm reluctant to do that)

    I was thinking something like read in the both the column values into two arrays, sort the required one and then 'organise' the first array and then write it back to the sheet.
    What do you think?

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Disjointed sort (Excel2003)

    Hi Rudi

    Unfortumately I also have several other disjointed 'blocks', BlockC, BlockD, .., etc
    Changing the A1:A20 from a 'data entry' value into a formula wouldn't suit my requirements.

    But I agree with the principle.

    zeddy

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Disjointed sort (Excel2003)

    No problem...it was the first idea that came to mind...

    PS: Based on what Hans mentioned....you do not need a "spare" column if you use the Shift key to move the column. As long as you hold down Shift, it will insert the selection pushing the other columns to the right. Same idea when you take it back. (You can also right click and drag the column choosing Shift right and Copy/Cut.

    regarding your idea. You could use formulas to link to the blocks and complie them into a list on another sheet. Then sort the formulas that are complied and once sorted, copy over the original data, pasting as values.
    Regards,
    Rudi

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

    Re: Disjointed sort (Excel2003)

    Perhaps you can adapt the macro in the attached sample workbook. It uses a bubble sort algorithm, so it will be slow on large datasets. With 20 rows, that won't be a problem.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Disjointed sort (Excel2003)

    Hi Hans/Rudi

    Many thanks to you both.
    I used a bit of info from both replies and now have a hunky-dory routine that does exactly what I want .
    Fantastic!

    zeddy

Posting Permissions

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