Forum Discussion
Change letter and numbers
Hello,
I have about 22000 lines with SKU product numbers from the manufacturer. I want to sell their products with my own SKU numbers, but I want my SKU numbers to be based on their SKU number. How can I do this?
Exemple
| My SKU | Manufacturer SKU | |
| TC6Q03 | T=E C=V 6=1 Q=a 0=0 3=5 | EV1A05 |
| TC6Q06 | T=E C=V 6=1 Q=a 0=0 6=1 | EV1A01 |
| TU6Q04 | EG1A03 | |
| VS6Q08 | WL1A02 | |
| RS401 | DL307 | |
| LI6Q02 | SH1A04 |
8 Replies
As variant that could be done by Power Query - split Manufacturer SKU texts to lists, expand to rows, merge with mapping table, combine back to texts
Query is in attached.
SergeiBaklan I believe something goes wrong in the "Expanded CharMap1" step and subsequent grouping (Text.Combine). I seems to throw the order of characters around. For example, "TQC603" should be "TC6Q03".
You are right, thank you. Forgot to fix table in memory before expanding. Here is by adding the index, Table.Buffer() will work as well.
nekbv If you are on a recent version of Excel that supports both TEXTJOIN and SEQUENCE, the attached workbook contains a working example.
Riny_van_Eekelen At home I use Microsoft Office 2010, but at work (where I need this xls) we use Libre last version. This is what I see when I use Libre.
My SKU Manufacturer SKU mfgcode mycode
EV1A05 #NAME? A Z
EV1A01 #NAME? B Y
EG1A03 #NAME? C X
WL1A02 #NAME? D W
DL307 #NAME? E V
SH1A04 #NAME? F U
G T
H D
I R
J Q
K P
L O
M N
N M
O L
P K
Q J
R I
S H
T G
U F
V E
W D
X C
Y B
Z A
0 9
1 8
2 7
3 6
4 5
5 4
6 3
7 2
8 1
9 0I don't think Libre has TEXTJOIN() and SEQUENCE(). It's better to ask Libre community how to solve the task.
