1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Quattro Pro/Excel - Sort within a cell

Discussion in 'Business Applications' started by stantley, Feb 20, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. stantley

    stantley Thread Starter

    Joined:
    May 22, 2005
    Messages:
    7,091
    What I want to do is have a word in one cell, then sort the letters of that word in alpha order and put the result in another cell. Some kind of alpha sort function. So that 'backed' would become 'abcdek'.

    Seems like it would be easy, but you know how that goes. Any ideas?
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You could try a macro/helper column combo. Try the attached.

    HTH
     

    Attached Files:

  3. stantley

    stantley Thread Starter

    Joined:
    May 22, 2005
    Messages:
    7,091
    Well, my first problem is I'm using Quattro Pro instead of Excel and I'm not having any luck trying to convert that .xls file to something Quattro Pro can use.

    My second problem is I don't have much experience using macros in Quattro Pro, so I'm a little lost here. Can you get me started?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    So, what made you choose that title for your thread? :confused:
     
  5. stantley

    stantley Thread Starter

    Joined:
    May 22, 2005
    Messages:
    7,091
    Well, most things you can do in Excel you can do in Quattro Pro and since more people have experience with Excel I didn't want to exclude them. If they had a solution in Excel, I thought I could translate it into something for Quattro Pro.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    All I can tell you is the Excel methodology I used.

    x = Len(Range("A1"))
    Get length of string to process, in A1.

    Range("A2").ClearContents
    Clear cell to output to (clean up from last run).

    Range("B1").Resize(x).FormulaR1C1 = "=MID(R1C1,ROW(),1)"
    Create formulas in B1:Bx to create 1st character, 2nd char, etc.

    Range("B1").Resize(x).Value = Range("B1").Resize(x).Value
    Convert B1:Bx (the formulas) to their values.

    Range("B1").Resize(x).Sort Key1:=Range("B1"), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Alpha-sort B1:Bx

    For i = 1 To x
    Range("A2") = Range("A2") & Cells(i, 2)
    Next i

    Concatentate B1:Bx values in A2.

    Range("B1").Resize(x).ClearContents
    Clear B1:Bx (clean up).

    HTH.
     
  7. stantley

    stantley Thread Starter

    Joined:
    May 22, 2005
    Messages:
    7,091
    It looks like Quattro Pro has the same functions so it might work.

    Thanks for your help. :)
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Actually that can be trimmed, by taking out the create formulas/hard-code formulas steps. In sections, for clarity, and with more "generic" syntax:

    x = Len(Cells(1, 1))
    Cells(2, 1).ClearContents

    For i = 1 To x
    Cells(i, 2) = Mid(Cells(1, 1), i, 1)
    Next i

    Cells(1, 2).Resize(x).Sort Key1:=Cells(1, 2), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    For i = 1 To x
    Cells(2, 1) = Cells(2, 1) & Cells(i, 2)
    Cells(i, 2).ClearContents
    Next i

    Good luck!
     
  9. stantley

    stantley Thread Starter

    Joined:
    May 22, 2005
    Messages:
    7,091
    I'm not having a lot of luck.

    Could that code be further simplified by knowing that the length will always equal to 6?
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/545608

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice