Forum Discussion
Help with complex string sorting in Excel
Hope someone can help me.
So I have some data entries that come in at :
'Port 1 Magnitude Index:0'
'Port 1 Magnitude Index:1'
'Port 1 Magnitude Index:2'
'Port 1 Phase Index:0'
'Port 1 Phase Index:1'
'Port 1 Phase Index 2',
'Port 2 Magnitude Index:0'
'Port 2 Magnitude Index:1'
'Port 2 Magnitude Index:2'
'Port 2 Phase Index:0'
'Port 2 Phase Index:1'
'Port 2 Phase Index 2'
Is there a way to sort the data so I can get it formatted as:
'Port 1 Magnitude Index:0'
'Port 1 Phase Index:0'
'Port 2 Magnitude Index:0'
'Port 2 Phase Index:0'
'Port 1 Magnitude Index:1'
'Port 1 Phase Index:1'
'Port 2 Magnitude Index:1'
'Port 2 Phase Index:1'
'Port 1 Magnitude Index:2'
'Port 1 Phase Index:2'
'Port 2 Magnitude Index:2'
'Port 2 Phase Index:2'
In the case that I am given, the data loops through Port 1 Magnitude (at all index) then Port 1 Phase, then Port 2 Magnitude, and then Port 2 Phase. But I'm trying to sort the data by viewing all of 'Index:0' first and then 'Index:1' etc.
Can anyone help?
3 Replies
Are there always 12 lines of data with the original data listed as in your example? This would mean that the data that contains "Index 0" always is in rows 1, 4, 7 and 10 (and so on with "Index 1" and "Index 2"). If so you can try the attached file. Click the button in cell F2 to start the macro.
Sub index() Dim i As Integer Dim z As Integer Dim w As Integer Dim v As Integer For i = 1 To 3 For z = 1 To 4 Cells(z + w, 4).Value = Cells(i + v, 1).Value v = v + 3 Next z v = 0 w = w + 4 Next i End SubIs this correct?
'Port 1 Phase Index 2',
without : before the 2, and with a comma at the end?
- Create a custom list and sort by the custom list.
Reference: https://support.microsoft.com/en-us/office/create-or-delete-a-custom-list-for-sorting-and-filling-data-d1cf624f-2d2b-44fa-814b-ba213ec2fd61#:~:text=Follow%20these%20steps%3A%201%20In%20a%20range%20of,the%20list%20that%20you%20have%20...%20See%20More?msclkid=98c45259b1fc11ec9408acca81c19bda
