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

#### MixedUpExcel

##### Board Regular
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Fluff

##### MrExcel MVP, Moderator
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

##### MrExcel MVP, Moderator
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

##### MrExcel MVP
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

##### Well-known Member
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

##### Board Regular
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

Replies
16
Views
208
Replies
8
Views
74
Replies
8
Views
79
Replies
5
Views
59
Replies
6
Views
83