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:

Product Code	Assoc. Code		Product Code	Assoc. Code		Product Code	Associ. Code
ABC123	AAA		JHG135	HHH		RKG134	OOO
XYS456	BBB		ABC123	III		EKT526	PPP
ABC123	CCC		JFH783	JJJ		QWE987	QQQ
MST564	DDD		MST563	KKK		ADD654	RRR
LRT753	EEE		PRT852	LLL		JHE483	SSS
ABC123	FFF		LRT753	MMM		ABC123	TTT
MST564	GGG		ABC963	NNN		PRT852	VVV

<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:

ABC123	AAA
ABC123	CCC
ABC123	FFF
ABC123	III
ABC123	TTT
ABC963	NNN
ADD654	RRR
EKT526	PPP
JFH783	JJJ
JHE483	SSS
JHG135	HHH
LRT753	EEE
LRT753	MMM
MST563	KKK
MST564	DDD
MST564	GGG
PRT852	LLL
PRT852	VVV
QWE987	QQQ
RKG134	OOO
XYS456	BBB

<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?

 

[Eric W]

Glad we could help.