Obtain all unique values from an alphanumeric sequence

[Tiago MF]

Hello,

I need a formula to obtain all the unique values from an alphanumeric sequence. Hopefully later I'll be able to adapt it for any kind of sequence but for now it can be for this particular sequence that can be broken into 3 sets: first and third sets have two letters each and each letter can be any from A to Z. second set has two numbers, each can range from 0 to 9. So something with this format "LL-NN-LL". Values from this sequence would be for example:

• TY-67-EP
• KA-03-WW

Now the formula needs to be something that I can drag down and it will show me all the possible combinations when we offset one of the members of the sequence.
So imagine, first value would be "AA-00-AA", next could be "AA-00-AB" and so on until "ZZ-99-ZZ".
My approach so far was having two named ranges, one called "alpha" containing all the letters from A to Z and another called "numeric" with all the numbers and then use a formula with INDEXes but that's where I got lost...I looked around and found something that was helping me with one of the lettered sets:

=INDEX(alpha;INT(MOD(ROWS(A$2:A2)/27;27))+1)&INDEX(alpha;MOD(ROWS(A$2:A2);27))

, but when I tried to extend it to include the other sets it started to become very confusing and I don't think I was getting the intended result..

Can someone please help?

If there's something about this request that isn't clear please tell me and I'll reformulate.

Much appreciated!

 

[NARAYANK991]

Hi ,

Your explanation is clear enough ; I am finding it difficult to visualize how your data is laid out ; you talk of a sequence , which is something that is present in one cell ; then you talk of dragging the formula down , which is something that is done when your data is spread out over many rows in a column.

If you can upload a sample file with a variety of data , everything will be clear.

Narayan

 

[Luke M]

Just as a note, using that setup, there are 26*26*100*26*26=
45,697,600
possible combinations. You don't have enough rows to display that many. But, to see how the pattern would work, see the attached, which has the first 2000.

 

[NARAYANK991]

Hi ,

Going by what Luke has uploaded , VBA would make it easy ; all you need to specify is the starting string , and the variable element , and the desired sequence would be generated.

It would make it easy if only one element out of the three were to vary ; thus generating a sequence starting from TY-67-EP and varying the last element , would generate all the possibilities from TY-67-EP through TY-67-ZZ. Varying the second element would generate all the possibilities from TY-67-EP through TY-99-EP , while varying the first element would generate all the possibilities from TY-67-EP through ZZ-67-EP.

Narayan

 

[Tiago MF]

Thanks Luke! That's where I imagined the formula was going but couldn't really get there, so that was a great help!

And yes, I realized it would be impossible to make all the results for that particular sequence but like I said the idea was to get a method I could adapt to make other sequences. Now with your method, if I wanted to use it for a smaller sequence, say for example just "AA-00" and all further variations, I would be able to generate all combinations on an excel sheet

For bigger sequences I guess I could use VBA. Like NARAYANK points out, it would be easy to generate sub-sets that I could have the macro copy&paste on various columns or even sheets and thus not suffer from the problem of row limitations.

Thanks a lot!