Sorting 3 columns (over 1million rows in total) A - Z

MixedUpExcel · Mar 14, 2019

Hi,

I have 3 workbooks where the total rows is greater than 1048576 rows.

It's probably about 1,500,000 rows (approx)

These are Product codes (starting with either a number or a letter) - (including duplicates but at this moment in time, I don't want to remove the duplicates due to the data in the adjacent column that will be sorted with it's corresponding code)

I will have 6 columns in total on 1 sheet (I'll manually take the 2 columns from each of the 3 workbooks above) - Columns 1 -2 , 3 - 4, 5 - 6.

I want to sort all of the codes into A-Z order (imagine the list could fit in a single column of 1,500,000 rows) but I want to then split it at about row 700,000 and put the remaining codes into a second column.

Is there some simple code that will allow me to do that?

Thanks.

Fluff · Mar 17, 2019

You could try this

Code:

Sub MixedUpExcel()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long, cc As Long
   Dim tmp1 As Variant, tmp2 As Variant, tmp3 As Variant
   
   Ary = Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To (UBound(Ary) * 3), 1 To 3)
   For c = 1 To 7 Step 3
      For r = 2 To UBound(Ary)
         If Ary(r, c) <> "" Then
            rr = rr + 1
            For cc = 1 To 3
               Nary(rr, cc) = Ary(r, c + cc - 1)
            Next cc
         End If
      Next r
   Next c
   c = rr
   For r = 1 To c
      For rr = r + 1 To c
         If Nary(rr, 1) < Nary(r, 1) Then
            tmp1 = Nary(r, 1): tmp2 = Nary(r, 2): tmp3 = Nary(r, 3)
            Nary(r, 1) = Nary(rr, 1): Nary(r, 2) = Nary(rr, 2): Nary(r, 3) = Nary(rr, 3)
            Nary(rr, 1) = tmp1: Nary(rr, 2) = tmp2: Nary(rr, 3) = tmp3
         End If
      Next rr
   Next r
   ReDim Ary(1 To (c / 2) + 1, 1 To 6)
   For rr = 1 To UBound(Ary)
      For cc = 1 To 3
         Ary(rr, cc) = Nary(rr, cc)
      Next cc
   Next rr
   r = 0
   For rr = UBound(Ary) + 1 To UBound(Nary)
      If Nary(rr, 1) <> "" Then
         r = r + 1
         For c = 4 To 6
            Ary(r, c) = Nary(rr, c - 3)
         Next c
      End If
   Next rr
   Range("L1").Resize(UBound(Ary), 6).Value2 = Ary
End Sub

BUT it will take some time with that amount of data, assuming it doesn't get overloaded & crash.

Fluff · Mar 17, 2019

For reference
~10,000 rows took ~30 seconds
~25,000 rows took ~4 minutes
so 1.5 million rows is going to take a VERY long time, if it doesn't crash.

shg · Mar 17, 2019

You want to merge three sets of two column ranges so the first column is sorted?

If you can load all six columns into memory (which you should be able to do), you should be able to Quicksort the array and then just spread the result over several columns.

Akuini · Mar 17, 2019

A workaround:
Put the data in a text editor (e.g Notepad++), do the sorting, then put it back to the workbook.
I tried sorting 2 million rows, it took about 9 seconds.

MixedUpExcel · Mar 21, 2019

Hi Akuni,

At the moment, I'm limited to just the software I have on my PC (which doesn't include Notepad++) although I might push my IT department for it if I can't find any other working solution.

Thanks for the suggestion. I like the sounds of '9 seconds'

Hi shg,

I'm not sure how to do what you've suggested, would you be able to point me in the right direction to be able to put the code together?

Thanks.

Hi Fluff,

Thanks for taking the time to put the code together.. however, a few of your phrases kinda put me off using it

BUT it will take some time with that amount of data, assuming it doesn't get overloaded & crash

so 1.5 million rows is going to take a VERY long time, if it doesn't crash

In Summary,

shg, please advise on above point - that is my first option.

Akuni, if I can't get the first option to work, I'll try my IT department

Fluff, I always like using code but if there is a quicker and easier way, either code or manually, I'll take that option.

Thanks again all.

Simon

Sorting 3 columns (over 1million rows in total) A - Z

MixedUpExcel

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

shg

MrExcel MVP

Akuini

Well-known Member

MixedUpExcel

Board Regular

Similar threads

Forum statistics

Share this page

Sorting 3 columns (over 1million rows in total) A - Z

MixedUpExcel

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

shg

MrExcel MVP

Akuini

Well-known Member

MixedUpExcel

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock