Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hello, this topic may have been covered before, but I can't seem to find it.

    Please see attached example image, and then I'll tell you what I'm trying to accomplish.

    [attachment=85446:untitled.JPG]

    I want to set up my spreed sheet so that if I click on the column title ("Color" or "Number" from my example) the data below would be rearranged in Alpha Numeric order.
    Thanks for your help.
    Attached Images Attached Images
    Time can fix anything.....even a broken clock. - Handy Andy

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As a starter, how about something like this...

    [attachment=85447ouble_C...ort_Data.xls]

    This uses seperate IF statements in the Workbook_SheetBeforeDoubleClick event that then calls different sub routines for the sort processes.

    I'm sure that other more experienced loungers will offer more elegant approaches.

    I have this set up for auto sorting 15 columns of data, (15 times over). I'd like to think this could be combined into one more efficient command, maybe... ???
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='792536' date='08-Sep-2009 11:20']As a starter, how about something like this...

    [attachment=85447ouble_C...ort_Data.xls]

    This uses seperate IF statements in the Workbook_SheetBeforeDoubleClick event that then calls different sub routines for the sort processes.

    I'm sure that other more experienced loungers will offer more elegant approaches.

    I have this set up for auto sorting 15 columns of data, (15 times over). I'd like to think this could be combined into one more efficient command, maybe... ???[/quote]


    I'm sorry I can't seem to get your spreed sheet to work, but thanks for your help anyway.
    Time can fix anything.....even a broken clock. - Handy Andy

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HandyAndy' post='792539' date='08-Sep-2009 16:36']I'm sorry I can't seem to get your spreed sheet to work, but thanks for your help anyway.[/quote]

    You will need to have macro's enabled. It works by double clicking either of the header cells.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HandyAndy' post='792539' date='08-Sep-2009 23:36']I'm sorry I can't seem to get your spreed sheet to work, but thanks for your help anyway.[/quote]


    I tried it and its works.

    Did you enable the macro when you first open the file and double click on the cell Color or Number
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='franciz' post='792545' date='08-Sep-2009 12:05']I tried it and its works.

    Did you enable the macro when you first open the file and double click on the cell Color or Number[/quote]


    Sorry about that, I tried it again and It works great! Thank you very much.
    Time can fix anything.....even a broken clock. - Handy Andy

  7. #7
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have a follow up on this topic.

    Now I would like to sort the columns, but I want the rows to stay locked together.
    I know this can be done I just don't know how.

    Hope you understand me, and thanks again.
    Time can fix anything.....even a broken clock. - Handy Andy

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry Andy, I'm not sure that I understand your question. Regardless of which sort is processed, the entire range is sorted accordingly.

  9. #9
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HandyAndy' post='792565' date='08-Sep-2009 14:41']I have a follow up on this topic.

    Now I would like to sort the columns, but I want the rows to stay locked together.
    I know this can be done I just don't know how.

    Hope you understand me, and thanks again.[/quote]


    To better explain myself please refer to my original example.

    I want to be able to sort the rows by clicking on the specific field in the column heading.

    So according to my previous example the row fields should always be intact (Red 24, Green 32, Blue 123, and so on)

    I want to be able to click on "Color" and have "Black 24" be at the top of the list. (Not "Black 24")
    In other words I want to sort the rows by column headings. All data in a given row should always be in the same row, only the row will move up or down.
    Time can fix anything.....even a broken clock. - Handy Andy

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maybe I am missing something here, but as far as i can see, the workbook that I posted fulfills those requirements.

    <<I want to be able to click on "Color" and have "Black 24" be at the top of the list. (Not "Black 24")

    Black 24 is at the top when sorted by column A.

  11. #11
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='792584' date='08-Sep-2009 16:21']Maybe I am missing something here, but as far as i can see, the workbook that I posted fulfills those requirements.

    <<I want to be able to click on "Color" and have "Black 24" be at the top of the list. (Not "Black 24")

    Black 24 is at the top when sorted by column A.[/quote]

    My mistake, sorry I am dealing with my horrible allergies.

    Let me try explaining again:

    I want each row to be able to move up or down, but I dont want the data in the rows getting scrambled.

    For example:
    Please look at example "one". Now example "two" shows what happens when I click on "Numbers". Example "three" is what I want to happen when I click on "Numbers".
    [attachment=85452:examples.JPG]

    Thanks again for all your help.
    Attached Images Attached Images
    Time can fix anything.....even a broken clock. - Handy Andy

  12. #12
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. Using the workbook that I posted earlier, I input your data (example One), exactly as you provided. When I double click the word Number in cell B1, I get a rearanged list of A-H in column A and 1-8 in column B, as per your example three.

    I can't see how you are getting the result in example two. You may need to post a stripped down version of your own workbook that provides this result.

    The code in the workbook that I posted sorts the entire rows of 2 to the last row with data in column A. If D and 4 are on the same row prior to sorting, they will remain on the same row after the sort.

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    An after thought:

    Are you by any chance using absolute formula's in either columns A or B?

  14. #14
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Somewhere Over the Rainbow
    Posts
    232
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='VegasNath' post='792599' date='08-Sep-2009 17:48']An after thought:

    Are you by any chance using absolute formula's in either columns A or B?[/quote]


    Ok, I got it to work. I am trying to add more columns to the equation using your example, but I am having trouble.
    See attached file. When I double click on column "C" nothing happens.
    Please check my macro and tell me what I did wrong.

    Thanks again.
    Attached Files Attached Files
    Time can fix anything.....even a broken clock. - Handy Andy

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andy,

    Code:
    Sub SortC()
    
    'This is called by double clicking "C1" - see Private Sub Workbook_SheetBeforeDoubleClick
    
    .........
    
    End Sub
    Your code is correct in the 'Module_sort', however you need to also create the caller in the 'This Workbook' object.

    Post back if you are still unsure.

    As I said previously, I'm sure that there is a much more elegant approach to this. I'm hoping others will chip in with better suggestions.

Page 1 of 2 12 LastLast

Posting Permissions

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