Oct 20 2020 11:27 PM - edited Oct 20 2020 11:29 PM
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 |
Oct 21 2020 12:30 AM
@nekbv If you are on a recent version of Excel that supports both TEXTJOIN and SEQUENCE, the attached workbook contains a working example.
Oct 21 2020 01:07 AM
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.
Oct 21 2020 01:46 AM
@Sergei Baklan 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".
Oct 21 2020 02:00 AM
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.
Oct 21 2020 02:42 AM
@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 0
Oct 21 2020 10:28 AM
I don't think Libre has TEXTJOIN() and SEQUENCE(). It's better to ask Libre community how to solve the task.
Oct 21 2020 11:02 PM - edited Oct 21 2020 11:10 PM
@Sergei Baklan Expression.Error: 5 arguments were passed to function which expects between 2 and 4.
Details:
Pattern=
Arguments=List
Oct 22 2020 01:47 AM - edited Oct 22 2020 01:48 AM
Fifth explicit argument defining the data type was added to Table.AddIndexColumn() recently, somewhere mid summer. On older versions of Power Query you shall delete it, but I'd recommend to update Power Query instead, otherwise you'll have compatibility issues in future.