Rearranging Words within a Cell

[sllaksvb]

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.2043520435

[Tom Jones]

Don't read carefully...

[sllaksvb]

Hi Tom,

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

[mdmackillop]

I suggest you post a workbook showing the expected outcome. I can't follow your logic: (tried reading carefully)

[sllaksvb]

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.

[SamT]

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.

[SamT]

Sub SwapCitiesInSameCell()
Dim Cities As Variant 'Array
Cities = Split(Cel, "To ")
Cel = Trim(Cities(1)) & " To " & Trim(Cities(0))
End Sub

[mdmackillop]

I cannot see any logic that puts DENVER TO DUBLIN in C2

[SamT]

Trying to "Save" all the Sheets Cells possible. They cost a fortune, DonchaKnow?

[sllaksvb]

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.

[sllaksvb]

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.

[SamT]

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.

[JKwan]

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

[SamT]

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

[Paul_Hossler]

I must be missing something, but why not a simple formula?

20440

[SamT]

Ooh, that's slick.
=IF(A2<=D2,B2,D2&" From "&A2)