list cells in range that include:

[Bobbo Jones]

Hi all! I have a problem that has been doing my head in! possibly someone here might be able to shed some light for me!?

I am working with a spreadsheet of products, all of which are only allowed to be sold in specific countries. each row is a different product.
Each row has a list of territory codes in Column K.
An example of what they look like is something like this: AU/NZ/BE/NL/LU
The cells can have anywhere from 10 to 60 different 2 letter codes, always separated by a forward slash.
What I am looking for, are the 'most common' 2 letter codes, to decide which products can be bundled together for sale.

EG,
Cell A1 contains: AU/NZ/BE/NL/LU
Cell A2 contains: NZ/AU/BE/OM/KW/PS/RU/
Cell A3 contains: OM/KW/PS/RU/AU/NZ/BE
Cell A4 contains: RU/OM/KW
Cell A5 contains: AU/NZ/BE/NL/LU/RU/OM/KW

So the most common codes are
AU/NZ/BE which all occur in Cells A1, A2, A3, and A5. So I could bundle a product with rows 1, 2, 3, and 5 for sale in Australia, New Zealand and Belgium. (product is digital)
OM/KW/RU also occurs 4 times, in cells A2, A3, A4, and A5.

SO that was difficult to figure out by eye with the above example.
I have 425 rows, and hundreds of different 2 letter codes. You can imgaine its pretty overwhelming!
Each row only has 1 cell with these codes in it, lets say column K.

I need way to learn something like the following, Rows 101 thru 117, 194, 298 thru 303 all have AU/NZ/BE/NL/LU/RU/OM/KW.
Then I can take all the data from those rows, and use them together.

I hope this makes sense?

[Bobbo Jones]

any help would be amazing!!!

[Hawkeye16]

I think your question is a lot more complicated than you may realize. Even with just 10 possible country codes there are millions of different possible combinations. Then you must count the occurrence of each one based on 400 input rows and rank them. This is no simple task.

If you had certain product market groups you had in mind to start it would make it a lot simpler, although it would still require a lot of helper columns and/or vba to accomplish.

[:) Sixthsense :)]

I think your question is a lot more complicated than you may realize

You expressed the situation perfectly

I thought to give a try... but after digging it further I am afraid to touch this post since it involves lot of permutation combinations!