Match up SKU with SKU variation
Firstly, good luck with this one lol. I'm sure there's a way of doing this, but for the life of me i can't even think of how to word it.
So, here goes: speaking in e-commerce terms, i have a list of products with their SKU (a unique code to distinguish it), and a list of product sizes. We are using Magento, so each product has a Main product, and you link each variation to that main product. (configurable and simple products for Magento techies)
Here's an example using SKU's:
A1000B - main product
A1000SB - size small
A1000MB - size medium
A1000LB - size large
A1000XLB - size xl
A1000 is the part no., S being Small, and B being Black.
SKU's end in either a number or letter.
SKU's are identical up until the size and colour.
SKU's are varying sizes.
I need to somehow match up the sizes to the main product.
The only way i see this working for all SKU's would be a partial vlookup of sorts. I have tried the approximate match vlookup (adding true instead of false) however this doesn't work as the SKU's are so similar.
I am completely lost on this. Any advice or help with this would be amazing and highly appreciated.
Thanks,
D
csv magento
asked Nov 9 '18 at 15:36
nadr
33
add a comment |
Firstly, good luck with this one lol. I'm sure there's a way of doing this, but for the life of me i can't even think of how to word it.
So, here goes: speaking in e-commerce terms, i have a list of products with their SKU (a unique code to distinguish it), and a list of product sizes. We are using Magento, so each product has a Main product, and you link each variation to that main product. (configurable and simple products for Magento techies)
Here's an example using SKU's:
A1000B - main product
A1000SB - size small
A1000MB - size medium
A1000LB - size large
A1000XLB - size xl
A1000 is the part no., S being Small, and B being Black.
SKU's end in either a number or letter.
SKU's are identical up until the size and colour.
SKU's are varying sizes.
I need to somehow match up the sizes to the main product.
The only way i see this working for all SKU's would be a partial vlookup of sorts. I have tried the approximate match vlookup (adding true instead of false) however this doesn't work as the SKU's are so similar.
I am completely lost on this. Any advice or help with this would be amazing and highly appreciated.
Thanks,
D
csv magento
asked Nov 9 '18 at 15:36
nadr
33
I'm guessing by your reference to vlookup you are hoping to achieve this and a spreadsheet?
– JonnyIrving
Nov 9 '18 at 15:40
Sorry yes, I am working from 2 spreadsheets, one with main products and one with just the sizes.
– nadr
Nov 9 '18 at 15:41
add a comment |
Firstly, good luck with this one lol. I'm sure there's a way of doing this, but for the life of me i can't even think of how to word it.
So, here goes: speaking in e-commerce terms, i have a list of products with their SKU (a unique code to distinguish it), and a list of product sizes. We are using Magento, so each product has a Main product, and you link each variation to that main product. (configurable and simple products for Magento techies)
Here's an example using SKU's:
A1000B - main product
A1000SB - size small
A1000MB - size medium
A1000LB - size large
A1000XLB - size xl
A1000 is the part no., S being Small, and B being Black.
SKU's end in either a number or letter.
SKU's are identical up until the size and colour.
SKU's are varying sizes.
I need to somehow match up the sizes to the main product.
The only way i see this working for all SKU's would be a partial vlookup of sorts. I have tried the approximate match vlookup (adding true instead of false) however this doesn't work as the SKU's are so similar.
I am completely lost on this. Any advice or help with this would be amazing and highly appreciated.
Thanks,
D
csv magento
asked Nov 9 '18 at 15:36
nadr
33
Firstly, good luck with this one lol. I'm sure there's a way of doing this, but for the life of me i can't even think of how to word it.
So, here goes: speaking in e-commerce terms, i have a list of products with their SKU (a unique code to distinguish it), and a list of product sizes. We are using Magento, so each product has a Main product, and you link each variation to that main product. (configurable and simple products for Magento techies)
Here's an example using SKU's:
A1000B - main product
A1000SB - size small
A1000MB - size medium
A1000LB - size large
A1000XLB - size xl
A1000 is the part no., S being Small, and B being Black.
SKU's end in either a number or letter.
SKU's are identical up until the size and colour.
SKU's are varying sizes.
I need to somehow match up the sizes to the main product.
The only way i see this working for all SKU's would be a partial vlookup of sorts. I have tried the approximate match vlookup (adding true instead of false) however this doesn't work as the SKU's are so similar.
I am completely lost on this. Any advice or help with this would be amazing and highly appreciated.
Thanks,
D
csv magento
csv magento
asked Nov 9 '18 at 15:36
nadr
33
asked Nov 9 '18 at 15:36
nadr
33
edited Nov 9 '18 at 15:49
asked Nov 9 '18 at 15:36
nadr
33
asked Nov 9 '18 at 15:36
nadr
33
asked Nov 9 '18 at 15:36
nadr
33
33
I'm guessing by your reference to vlookup you are hoping to achieve this and a spreadsheet?
– JonnyIrving
Nov 9 '18 at 15:40
Sorry yes, I am working from 2 spreadsheets, one with main products and one with just the sizes.
– nadr
Nov 9 '18 at 15:41
add a comment |
I'm guessing by your reference to vlookup you are hoping to achieve this and a spreadsheet?
– JonnyIrving
Nov 9 '18 at 15:40
Sorry yes, I am working from 2 spreadsheets, one with main products and one with just the sizes.
– nadr
Nov 9 '18 at 15:41
I'm guessing by your reference to vlookup you are hoping to achieve this and a spreadsheet?
– JonnyIrving
Nov 9 '18 at 15:40
I'm guessing by your reference to vlookup you are hoping to achieve this and a spreadsheet?
– JonnyIrving
Nov 9 '18 at 15:40
Sorry yes, I am working from 2 spreadsheets, one with main products and one with just the sizes.
– nadr
Nov 9 '18 at 15:41
Sorry yes, I am working from 2 spreadsheets, one with main products and one with just the sizes.
– nadr
Nov 9 '18 at 15:41
add a comment |
1 Answer
1
active
oldest
votes
I'm not a 100% is this is the kind of thing you're looking for but you could potentially use excel SUBSTITUTE on the SKU to remove the size data then use that 'main product' to filter your SKUs by.
In the below image I've created a basic data set which might look like a simplified version of your SKU data. In the Helper Col I've added a function which essentially just removes the size characters of the SKU, I've done this using nested SUBSTITUTE statements which aren't the neatest looking things in the world but should do the trick.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"XLB",""),"LB",""),"MB",""),"SB",""),"B","")
Each SUBSTITUTE is essentially doing a find and replace of the keyword in that cell and replacing it with a blank "". The order is important because you want to replace XLB first because if you tried to match just B first you'd end up with some cells showing A100XL.
It wasn't clear in the question if you wanted to keep the B or not, as you talk about both the 'main product' and also the 'part number'. However using more nested statements you could keep the B by replacing it back in instead of a "".
You could then use this column to filter by your products and see all the appropriate SKUs
Hope this helps!
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53228781%2fmatch-up-sku-with-sku-variation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'm not a 100% is this is the kind of thing you're looking for but you could potentially use excel SUBSTITUTE on the SKU to remove the size data then use that 'main product' to filter your SKUs by.
In the below image I've created a basic data set which might look like a simplified version of your SKU data. In the Helper Col I've added a function which essentially just removes the size characters of the SKU, I've done this using nested SUBSTITUTE statements which aren't the neatest looking things in the world but should do the trick.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"XLB",""),"LB",""),"MB",""),"SB",""),"B","")
Each SUBSTITUTE is essentially doing a find and replace of the keyword in that cell and replacing it with a blank "". The order is important because you want to replace XLB first because if you tried to match just B first you'd end up with some cells showing A100XL.
It wasn't clear in the question if you wanted to keep the B or not, as you talk about both the 'main product' and also the 'part number'. However using more nested statements you could keep the B by replacing it back in instead of a "".
You could then use this column to filter by your products and see all the appropriate SKUs
Hope this helps!
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
add a comment |
I'm not a 100% is this is the kind of thing you're looking for but you could potentially use excel SUBSTITUTE on the SKU to remove the size data then use that 'main product' to filter your SKUs by.
In the below image I've created a basic data set which might look like a simplified version of your SKU data. In the Helper Col I've added a function which essentially just removes the size characters of the SKU, I've done this using nested SUBSTITUTE statements which aren't the neatest looking things in the world but should do the trick.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"XLB",""),"LB",""),"MB",""),"SB",""),"B","")
Each SUBSTITUTE is essentially doing a find and replace of the keyword in that cell and replacing it with a blank "". The order is important because you want to replace XLB first because if you tried to match just B first you'd end up with some cells showing A100XL.
It wasn't clear in the question if you wanted to keep the B or not, as you talk about both the 'main product' and also the 'part number'. However using more nested statements you could keep the B by replacing it back in instead of a "".
You could then use this column to filter by your products and see all the appropriate SKUs
Hope this helps!
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
add a comment |
I'm not a 100% is this is the kind of thing you're looking for but you could potentially use excel SUBSTITUTE on the SKU to remove the size data then use that 'main product' to filter your SKUs by.
In the below image I've created a basic data set which might look like a simplified version of your SKU data. In the Helper Col I've added a function which essentially just removes the size characters of the SKU, I've done this using nested SUBSTITUTE statements which aren't the neatest looking things in the world but should do the trick.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"XLB",""),"LB",""),"MB",""),"SB",""),"B","")
Each SUBSTITUTE is essentially doing a find and replace of the keyword in that cell and replacing it with a blank "". The order is important because you want to replace XLB first because if you tried to match just B first you'd end up with some cells showing A100XL.
It wasn't clear in the question if you wanted to keep the B or not, as you talk about both the 'main product' and also the 'part number'. However using more nested statements you could keep the B by replacing it back in instead of a "".
You could then use this column to filter by your products and see all the appropriate SKUs
Hope this helps!
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
I'm not a 100% is this is the kind of thing you're looking for but you could potentially use excel SUBSTITUTE on the SKU to remove the size data then use that 'main product' to filter your SKUs by.
In the below image I've created a basic data set which might look like a simplified version of your SKU data. In the Helper Col I've added a function which essentially just removes the size characters of the SKU, I've done this using nested SUBSTITUTE statements which aren't the neatest looking things in the world but should do the trick.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"XLB",""),"LB",""),"MB",""),"SB",""),"B","")
Each SUBSTITUTE is essentially doing a find and replace of the keyword in that cell and replacing it with a blank "". The order is important because you want to replace XLB first because if you tried to match just B first you'd end up with some cells showing A100XL.
It wasn't clear in the question if you wanted to keep the B or not, as you talk about both the 'main product' and also the 'part number'. However using more nested statements you could keep the B by replacing it back in instead of a "".
You could then use this column to filter by your products and see all the appropriate SKUs
Hope this helps!
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
edited Nov 12 '18 at 14:07
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
answered Nov 9 '18 at 15:58
JonnyIrving
4612616
4612616
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
add a comment |
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
Hi Jonny, thanks for the answer! Unfortunately, the SKUs can vary in length, and can also start with 2 letters instead of 1. This means any sort of blanket method to reduce the size, or pull a section of the code wouldn't work for them all :(.
– nadr
Nov 9 '18 at 16:04
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53228781%2fmatch-up-sku-with-sku-variation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm guessing by your reference to vlookup you are hoping to achieve this and a spreadsheet?
– JonnyIrving
Nov 9 '18 at 15:40
Sorry yes, I am working from 2 spreadsheets, one with main products and one with just the sizes.
– nadr
Nov 9 '18 at 15:41