I have a large list of product ID’s setup up in the following format;
SKU 1002NAV 1003BLU 1007PNB 1008NAV 1009NAV 1010BLK 1010BLU 1011BLU 1011BUR 1011GRY 1011NAV
The first 4 numbers representing the style of product and corresponding 3 letters representing the colour, as a result any products that have the same 4 number style ID will be identical products (just different colours)
I would like to return a list of all full product ID’s that have multiple styles in order to group them, so the above list would return something like;
SKU 1010BLK 1010BLU 1011BLU 1011BUR 1011GRY 1011NAV
It would ignore the other SKU’s such as 1002NAV
as there is only one style of this product.
I have a little knowledge of python as well if this cannot be done in excel but would prefer to keep it all within excel if possible.
Thank you.
Advertisement
Answer
Assuming your input is a list of unique SKU’s, try:
Formula in B1
:
=FILTER(A1:A11,COUNTIF(A1:A11,LEFT(A1:A11,4)&"*")>1,"")