Forum Discussion

Odintsov_Maksim's avatar
Odintsov_Maksim
Copper Contributor
May 23, 2020
Solved

Large amount of data sorting

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.
BI & Data Analysis
Macros and VBA
  • Jos_Woolley's avatar
    Jos_Woolley
    May 26, 2020

    Odintsov_Maksim 

     

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

     

    If MyCol <> 1 Then

    to

    If MyCol > 2 Then

     

    Regards

     

     

wumolad's avatar
wumolad
Iron Contributor
May 23, 2020

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

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    May 24, 2020

    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

    • Odintsov_Maksim's avatar
      Odintsov_Maksim
      Copper Contributor
      May 24, 2020
      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?
      • Jos_Woolley's avatar
        Jos_Woolley
        Iron Contributor
        May 24, 2020

        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

         

  • wumolad's avatar
    wumolad
    Iron Contributor
    May 24, 2020

    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's avatar
      Odintsov_Maksim
      Copper Contributor
      May 24, 2020
      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.

Resources