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

[MixedUpExcel]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Assoc. Code[/TD]
[TD][/TD]
[TD]Product Code[/TD]
[TD]Assoc. Code[/TD]
[TD][/TD]
[TD]Product Code[/TD]
[TD]Associ. Code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]JHG135[/TD]
[TD]HHH[/TD]
[TD][/TD]
[TD]RKG134[/TD]
[TD]OOO[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XYS456[/TD]
[TD]BBB[/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD]III[/TD]
[TD][/TD]
[TD]EKT526[/TD]
[TD]PPP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]CCC[/TD]
[TD][/TD]
[TD]JFH783[/TD]
[TD]JJJ[/TD]
[TD][/TD]
[TD]QWE987[/TD]
[TD]QQQ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MST564[/TD]
[TD]DDD[/TD]
[TD][/TD]
[TD]MST563[/TD]
[TD]KKK[/TD]
[TD][/TD]
[TD]ADD654[/TD]
[TD]RRR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LRT753[/TD]
[TD]EEE[/TD]
[TD][/TD]
[TD]PRT852[/TD]
[TD]LLL[/TD]
[TD][/TD]
[TD]JHE483[/TD]
[TD]SSS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC123[/TD]
[TD]FFF[/TD]
[TD][/TD]
[TD]LRT753[/TD]
[TD]MMM[/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD]TTT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MST564[/TD]
[TD]GGG[/TD]
[TD][/TD]
[TD]ABC963[/TD]
[TD]NNN[/TD]
[TD][/TD]
[TD]PRT852[/TD]
[TD]VVV[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]ABC123[/TD]
[TD="class: xl63, width: 64"]AAA[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC123[/TD]
[TD="class: xl63"]CCC[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC123[/TD]
[TD="class: xl63"]FFF[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC123[/TD]
[TD="class: xl63"]III[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC123[/TD]
[TD="class: xl63"]TTT[/TD]
[/TR]
[TR]
[TD="class: xl63"]ABC963[/TD]
[TD="class: xl63"]NNN[/TD]
[/TR]
[TR]
[TD="class: xl63"]ADD654[/TD]
[TD="class: xl63"]RRR[/TD]
[/TR]
[TR]
[TD="class: xl63"]EKT526[/TD]
[TD="class: xl63"]PPP[/TD]
[/TR]
[TR]
[TD="class: xl63"]JFH783[/TD]
[TD="class: xl63"]JJJ[/TD]
[/TR]
[TR]
[TD="class: xl63"]JHE483[/TD]
[TD="class: xl63"]SSS[/TD]
[/TR]
[TR]
[TD="class: xl63"]JHG135[/TD]
[TD="class: xl63"]HHH[/TD]
[/TR]
[TR]
[TD="class: xl63"]LRT753[/TD]
[TD="class: xl63"]EEE[/TD]
[/TR]
[TR]
[TD="class: xl63"]LRT753[/TD]
[TD="class: xl63"]MMM[/TD]
[/TR]
[TR]
[TD="class: xl63"]MST563[/TD]
[TD="class: xl63"]KKK[/TD]
[/TR]
[TR]
[TD="class: xl63"]MST564[/TD]
[TD="class: xl63"]DDD[/TD]
[/TR]
[TR]
[TD="class: xl63"]MST564[/TD]
[TD="class: xl63"]GGG[/TD]
[/TR]
[TR]
[TD="class: xl63"]PRT852[/TD]
[TD="class: xl63"]LLL[/TD]
[/TR]
[TR]
[TD="class: xl63"]PRT852[/TD]
[TD="class: xl63"]VVV[/TD]
[/TR]
[TR]
[TD="class: xl63"]QWE987[/TD]
[TD="class: xl63"]QQQ[/TD]
[/TR]
[TR]
[TD="class: xl63"]RKG134[/TD]
[TD="class: xl63"]OOO[/TD]
[/TR]
[TR]
[TD="class: xl63"]XYS456[/TD]
[TD="class: xl63"]BBB[/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[Eric W]

Glad we could help.