Consulting

Results 1 to 16 of 16

Thread: Rearranging Words within a Cell

  1. #1
    VBAX Regular
    Joined
    Aug 2017
    Posts
    52
    Location

    Rearranging Words within a Cell

    Hi all,

    I've been thinking of how to write code for this particular project, but to no avail. This is what I hope to achieve:

    To populate Denver to Dublin in C2, Amsterdam to Dublin in C3, Amsterdam to Dublin in C4, all the way down column C.
    The reason I am having trouble with this is that it the string in column C has to be according to alphabetical order, not according to Origin to Destination (e.g. even though Amsterdam is my destination, and Dublin is my origin, it should populate Dublin to Amsterdam in C3:C5)
    I need to repeat this all the way down column C for all corresponding fields in column A and D.

    Any help on this matter would be greatly appreciated! Thank you.countrysamp.xlsxcountrysamp.xlsx

  2. #2
    Don't read carefully...

  3. #3
    VBAX Regular
    Joined
    Aug 2017
    Posts
    52
    Location
    Hi Tom,

    Thank you for your prompt reply. I was thinking of creating a code in VBA to handle this issue. Any ideas how?

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I suggest you post a workbook showing the expected outcome. I can't follow your logic: (tried reading carefully)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Regular
    Joined
    Aug 2017
    Posts
    52
    Location
    Hi mdmackillop,

    Thank you for your reply. I am trying to achieve Column C, using Column A and D's data. Basically cell C has to be in alphabetical order, regardless of origin/destination. Hope you can understand.
    Attached Files Attached Files

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Add two columns After C. In C1 put "From", In new E1 put "To". In New D2 and Down put "3 4" and Use the WebDings Font.

    In C2 "Denver", in E2, "Amsterdam"

    USe VBA so that DoubleClicking D2 will swap the values of C2 and E2, If needed, also sort the table after the swap.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Sub SwapCitiesInSameCell()
    Dim Cities As Variant 'Array
    Cities = Split(Cel, "To ")
    Cel = Trim(Cities(1)) & " To " & Trim(Cities(0))
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I cannot see any logic that puts DENVER TO DUBLIN in C2
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Trying to "Save" all the Sheets Cells possible. They cost a fortune, DonchaKnow?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Regular
    Joined
    Aug 2017
    Posts
    52
    Location
    Mdmackillop, basically it should put whichever city that comes alphabetically first before " TO " and the city that comes alphabetically next after " TO ", regardless of origin/destination.

    Sorry if you are having trouble understanding, might not have the perfect language in describing what I'm trying to achieve. Regardless, thank you for your time and effort.

  11. #11
    VBAX Regular
    Joined
    Aug 2017
    Posts
    52
    Location
    SamT, thank you for your reply. I'm not sure I understand what your code is meant to do and it populates an error "subscript out of range" when I attempt to run it. Regardless, thank you for your time and effort, really appreciate it.

  12. #12
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I'm not sure I understand what your code is meant to do
    It's meant to provide clues to how you can write procedures that do what you require.

    Obviously, it won't works as is, because it's not even complete.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #13
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    give this a try, too lazy to code a general solution. I only code to your sample set and I put the result in column E instead of over writing C
    Sub test()
        For i = 2 To 12
            Select Case Cells(i, "A") > Cells(i, "D")
                Case True
                    Cells(i, "E") = Cells(i, "D") & " To " & Cells(i, "A")
                    
                Case Else
                    Cells(i, "E") = Cells(i, "A") & " To " & Cells(i, "D")
            End Select
        Next i
    End Sub

  14. #14
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Basically cell C has to be in alphabetical order, regardless of origin/destination. Hope you can understand.
    Reading Carefully, I see that you say that want the Cell to read:
    "__AabDdeilmmnorsttu", Amsterdam to Dublin in Alphabetical order of characters with Spaces first..


    Seriously, looking over your Attachment, I think what you are trying to say is that, of the two cities, Origin and Destination, you need the Alphabetically "earliest" one in the Origin City Position

    Sub SwapCitiesalphabeticallyInSameCell() 
        Dim Cities As Variant 'Array
    Dim Cel As Range
    Dim List As Range
    
    With Sheets("Sheet1")
    Set List = Range(.Range("C2"), .Range("C2").End(xlDown))
    End With
    
    For Each Cel in List
        Cities = Split(Cel, "To ") 
    If Trim(Cities(0)) > Trim(Cities(1)) Then 
        Cel = Trim(Cities(1)) & " From " & Trim(Cities(0)) '<---------- From?
    End If
    Next
    End Sub
    Last edited by SamT; 09-22-2017 at 03:14 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I must be missing something, but why not a simple formula?

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Ooh, that's slick.
    =IF(A2<=D2,B2,D2&" From "&A2)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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