MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 222
- Office Version
- 365
- Platform
- Windows
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:
<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:
<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?
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?