How to return list of all values that have matching first 4 characters

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.

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,"")

Advertisement

Answer