3 lists into 1, sorted and then split into 2 lists again

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
206
Hi,

Please note the following request will be to action this only in Excel - I'm on a works pc and don't have other software available to do this.

I have 3 lists of product codes.

The codes are in a random order and can be duplicated across the the 3 columns.

Each product code has an associated code which needs to remain in the column next to the product code it was originally next to.

Example below:

Product CodeAssoc. CodeProduct CodeAssoc. CodeProduct CodeAssoci. Code
ABC123AAAJHG135HHHRKG134OOO
XYS456BBBABC123IIIEKT526PPP
ABC123CCCJFH783JJJQWE987QQQ
MST564DDDMST563KKKADD654RRR
LRT753EEEPRT852LLLJHE483SSS
ABC123FFFLRT753MMMABC123TTT
MST564GGGABC963NNNPRT852VVV

<tbody>
</tbody>

I basically need to get column 4 & 5 under columns 1 & 2 and column 7 & 8 under columns 1 & 2 (making it a single column with an adjacent column with the Associated Products.

I then need to sort the product code (column 1) in alphabetical order with column 2 staying next to the code it's originally next to, like it would in a manual sort.

The end result would be:

ABC123AAA
ABC123CCC
ABC123FFF
ABC123III
ABC123TTT
ABC963NNN
ADD654RRR
EKT526PPP
JFH783JJJ
JHE483SSS
JHG135HHH
LRT753EEE
LRT753MMM
MST563KKK
MST564DDD
MST564GGG
PRT852LLL
PRT852VVV
QWE987QQQ
RKG134OOO
XYS456BBB

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

All this is straight forward to do with vba coding.

The problem I have is Column 1 = 500,000 products; Column 4 = 500,000 products; Column 7 = 400,000 products. Total is more than 1,048,576 rows.

Once it is all sorted, it will then need to be split over 2 (4 including associated codes) columns (due to exceeding the Excel Row limit)

Suggestions please?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Have you checked for duplicates? Make strings of Col A & ", " & Col B, then remove any duplicates and then split by "," repeat for other columns, then review your reduced data sets.

Or with coding, find a way to read the 3 pairs of columns into a single 2 column array (suspect some redim requirements - only the 2nd dimension can be changed so you may also need to use Application.Transpose for input/output).

There is plenty of online code showing how to sort an array (usually a bubble sort)

Then print the contents out with some kind of "IF x >= Rows.Count - 1000 then move to next column" as each loop iteration finishes.

I'm guessing you'll need to research how to "stitch" arrays together (try searching for write non continguous ranges to same array or something like that) and sorting an array.

You can probably use Application.Index with the array to determine it's mid point and then write out across 4 columns.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
When I've run into the need for more than 1+ million rows (whoever thought that would happen?) I head to either ACCESS or an array processing language like APL then, as needed, move the results back to Excel.
My personal preference is APL because it's so each to handle arrays (i.e., VERY large matrices).
 

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
206
Hi JackDanIce and kweaver,

Thank you both for your suggestions.

I'll look into both and see what I can do.

Thanks.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707

ADVERTISEMENT

Use PowerQuery, built into Excel, and load the entire data set into the Data Model.
Total rows in the Data Model is only limited by available memory.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I think PowerQuery only because DataModel increase size of the excel file
DataModel can be used after any transformation of data in PQ (if necessary)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640

ADVERTISEMENT

With that much data, you really should look into a more robust tool than Excel. However, just for fun I wrote up a macro that should do what you want.

Rich (BB code):
Sub CombineAndSort()
Dim MyCols As Variant, MyResult As Range, r As Variant, sl As Variant
Dim MyData As Variant, i As Long, rw As Long, Output() As Variant
Dim MaxRows As Long
t = Timer
    MyCols = Array(Sheets("Sheet3").Range("A:B"), _
                   Sheets("Sheet3").Range("D:E"), _
                   Sheets("Sheet3").Range("G:H"))
                   
    Set MyResult = Sheets("Sheet4").Range("A:A")
    MaxRows = 500000
    
    On Error GoTo Oops:
    Set sl = CreateObject("System.Collections.SortedList")
    
    On Error Resume Next
    For Each r In MyCols
        lr = r.Resize(1, 1).Offset(Rows.Count - 1).End(xlUp).Row
        MyData = r.Resize(lr, 2).Value
        For i = 2 To UBound(MyData)
            sl.Add MyData(i, 1) & "|" & MyData(i, 2), 1
        Next i
    Next r
    
    rw = 0
    
    ReDim Output(0 To MaxRows, 1 To 2)
    For i = 0 To sl.Count - 1
        rw = rw + 1
        r = Split(sl.getkey(i), "|")
        Output(rw, 1) = r(0)
        Output(rw, 2) = r(1)
        If rw = MaxRows Then
            Output(0, 1) = "Product Code"
            Output(0, 2) = "Associ. Code"
            MyResult.Resize(MaxRows + 1, 2).Value = Output
            ReDim Output(0 To MaxRows, 1 To 2)
            rw = 0
            Set MyResult = MyResult.Offset(, 3)
        End If
    Next i
    
    If rw > 0 Then
        Output(0, 1) = "Product Code"
        Output(0, 2) = "Associ. Code"
        MyResult.Resize(MaxRows + 1, 2).Value = Output
    End If
    Set sl = Nothing
    Debug.Print Timer - t
    Exit Sub
Oops:
    Debug.Print Err.Description
End Sub
Change the parts in red to match your sheet. I ran this on some sample data I created that has 500,000 and 500,000 and 400,000 rows, like yours. It took nearly an hour. Your mileage may vary.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
Per Eric W: I, too, created some sample data with 1,500,000 entries but I did this in APL. I ran your sort and split the data and it was about 2 seconds, if that.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
kweaver: That just goes to show that using the right tool can have enormous importance, which is what I stated. However, in thinking about it, even in VBA, I thought that using a SortedArray might not be the right tool. It has to sort the elements every time you insert something. I thought using an ArrayList might be faster, since you'd only need to sort it once. And in fact, that turned out to be the case. This version of the macro ran in about 24 seconds. Not quite up to your APL speed, but far better than an hour.

Code:
Sub CombineAndSort()
Dim MyCols As Variant, MyResult As Range, r As Variant, al As Variant
Dim MyData As Variant, i As Long, rw As Long, Output() As Variant
Dim MaxRows As Long, ta As Variant
t = Timer
    MyCols = Array(Sheets("Sheet3").Range("A:B"), _
                   Sheets("Sheet3").Range("D:E"), _
                   Sheets("Sheet3").Range("G:H"))
                   
    Set MyResult = Sheets("Sheet4").Range("A:A")
    MaxRows = 500000
    
    On Error GoTo Oops:
    Set al = CreateObject("System.Collections.ArrayList")
    
    On Error Resume Next
    For Each r In MyCols
        lr = r.Resize(1, 1).Offset(Rows.Count - 1).End(xlUp).Row
        MyData = r.Resize(lr, 2).Value
        For i = 2 To UBound(MyData)
            al.Add MyData(i, 1) & "|" & MyData(i, 2)
        Next i
    Next r
    
    rw = 0
    
    ReDim Output(0 To MaxRows, 1 To 2)
    al.Sort
    ta = al.toarray
    For i = 0 To al.Count - 1
        rw = rw + 1
        r = Split(ta(i), "|")
        Output(rw, 1) = r(0)
        Output(rw, 2) = r(1)
        If rw = MaxRows Then
            Output(0, 1) = "Product Code"
            Output(0, 2) = "Associ. Code"
            MyResult.Resize(MaxRows + 1, 2).Value = Output
            ReDim Output(0 To MaxRows, 1 To 2)
            rw = 0
            Set MyResult = MyResult.Offset(, 3)
        End If
    Next i
    
    If rw > 0 Then
        Output(0, 1) = "Product Code"
        Output(0, 2) = "Associ. Code"
        MyResult.Resize(MaxRows + 1, 2).Value = Output
    End If
    Set al = Nothing
    Debug.Print Timer - t
    Exit Sub
Oops:
    Debug.Print Err.Description
End Sub
 

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
206
Hi All,

Sorry for the late response - back at work today to check them out.

Thank you for your suggestions.

As it looks like some of the suggestions would be add-on's.. I will have to look at them as a last resort as I would need to get the administrator to install those for me.

Eric,

Thank you for the 2 pieces of code.

I ran the first one and it did appear to be taking a while, so I stopped that and ran your second piece. :)

Thank you for that - the second piece of code was so quick.

I had created a less tidy piece of code which was basically just splitting columns, adding to new columns, sorting ... did that a few times and it got me where I wanted but yours is much better.

Thank you again to all, really appreciate the suggestions and solutions.

Simon
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top