SOLVED

Large amount of data sorting

Copper Contributor

Please, help.

Need to sort numeric data contained in large numbers (several thousand) strings.

The data in each string must be sorted separately.

How can this be done quickly.

16 Replies

Hi @Odintsov_Maksim 

 

it might be difficult to visualize what you are trying to achieve without showing a sample data. Are you able to show a sample data after removing sensitive information?

 

Cheers

@wumolad 

Odintsov_Maksim_0-1590273923378.png

 

Hi @Odintsov_Maksim 

 

Is it possible to explain the exact procedure to perform to differentiate the unsorted cases and sorted cases?

 

For example, do you need to delete some rows or columns? 

 

Also, are you sorting column or row wise? Somehow difficult to understand what your intention is.

 

Cheers

@Odintsov_Maksim 

 

Sub SortAllRows()

Dim LRow    As Long
Dim i       As Long
Dim MyRow   As Range
Dim MyKey   As Range

With ThisWorkbook.Worksheets("Sheet1")
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    For i = 1 To LRow
        Set MyRow = .Range("B1:I1").Offset(i - 1, 0)
        Set MyKey = Application.Index(MyRow, 1)
        MyRow.Sort Key1:=MyKey, Order1:=xlAscending, Orientation:=xlLeftToRight
    Next i
End With

End Sub

 

Regards

I'm sorting cases in each rows separately.
In other words, this is sorting columns, but only within each individual row.
The task to delete something is absent.
Is this a VB code? Will it work like a macro, can I include it in an existing macro in edit mode?
Will this sort the cases in each row as I described?

@Odintsov_Maksim 

 

Yes, it's VBA. Of course it can be incorporated within an existing procedure that you have, though I'd suggest testing it as a standalone macro first, to check it's giving you the desired results. Currently it is set up to work on ranges beginning with B1:I1 and successive rows, just as per your screenshot. Obviously amend as required.

 

Regards

 

Yes, thanks, the code works. But the process stops when only empty cells are present in the sorting range.
It would be better to establish control of macro completion by column "A", in which there are always values. How to do it?
And further. Perhaps the macro could be improved if there is't specify a specific sorting range, but the current row as a whole. And is it possible not to specify the sheet name so that the macro runs on any Excel sheet?
I am not good in VBA syntax, sorry.

@Odintsov_Maksim 

 

Re "the process stops when only empty cells are present in the sorting range. It would be better to establish control of macro completion by column "A", in which there are always values.", I'm confused: it is precisely column A which controls completion of the macro: the code I supplied will process all rows from row 1 up to the row in the worksheet which contains the last non-blank entry in column A. As such, I'm not sure what you mean: did you change anything in the code I provided? 

Re "Perhaps the macro could be improved if there is't specify a specific sorting range, but the current row as a whole." I would never code a macro to process more cells than are necessary, and certainly not an entire row's worth (16384 cells). If the last column to be processed is subject to change, this can be detected as part of the macro, which is far better than blanket-setting it to the very end of the worksheet.

 

Re "And is it possible not to specify the sheet name so that the macro runs on any Excel sheet?" Absolutely. If you could clarify the confusion re my first point above with respect to your not thinking the process is controlled by the entries in column A then I'll happily post a revised version of the code.

 

Perhaps you should consider posting an actual workbook (with dummy data as opposed to real data, if necessary).

 

Regards

@Jos_Woolley 

About the first point. Sorry, I made a mistake. Everything works.
Regarding the second point. I did not mean expanding the diapason of cells to the maximum size, but using a function such as selecting a row (shift + space from the keyboard) and sorting with a heading from column "A". Maybe I'm wrong, and in macros it's better not to do that.
Example attached below.

@Odintsov_Maksim 

 

Not entirely sure what you mean there, sorry.

 

The modified version below detects the last non-empty cell in each row and applies the sort on a range with that cell as the upper bound. Note that, if any of the blank cells in your range in fact contain the null string (""), e.g. as a result of formulas within those cells, then it will not give correct results - in that case let me know and I will modify the code.

 

I have also amended it so that it runs on whichever is the active sheet at the time of running.

 

Sub SortAllRows()

Dim LRow    As Long
Dim i       As Long
Dim MyRow   As Range
Dim MyCol   As Long
Dim MyKey   As Range

With ActiveSheet
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    For i = 3 To LRow
        MyCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
        If MyCol <> 1 Then
            Set MyRow = .Cells(i, 2).Resize(, MyCol - 1)
            Set MyKey = Application.Index(MyRow, 1)
            MyRow.Sort Key1:=MyKey, Order1:=xlAscending, Orientation:=xlLeftToRight
        End If
    Next i
End With

End Sub

 

Regards

Well thank you.
It works.
Only one problem.
Column "A" contains the numbering of records. And column “A” should not be involved in the sorting procedure. The data from column "A" should always remain in the same place.
Regards, thanks

@Odintsov_Maksim 

 

Column A should not be affected whatsoever by the macro I posted. Can you post a workbook with an example in which column A is affected?

 

Regards

@Jos_Woolley File attached

best response confirmed by Odintsov_Maksim (Copper Contributor)
Solution

@Odintsov_Maksim 

 

Apologies. You're correct. Small adjustment needed: change

 

If MyCol <> 1 Then

to

If MyCol > 2 Then

 

Regards

 

 

OK, worked.
Will be testing.
Thank you so much!
1 best response

Accepted Solutions
best response confirmed by Odintsov_Maksim (Copper Contributor)
Solution

@Odintsov_Maksim 

 

Apologies. You're correct. Small adjustment needed: change

 

If MyCol <> 1 Then

to

If MyCol > 2 Then

 

Regards

 

 

View solution in original post