Macro to ORDER unique combinations

[woodsquash]

Hi

I'm not sure if this is possible, but here goes.

I have three columns of letters

Screenshot (1).jpg

Column A is my list of 5 variables
Columns B and C are my list of unique two digit combinations of column A (done using a macro)

In column D, with the first row (A and B) being combination number 1, I would like to order the list of columns B and C such that the next on the list is the first combination not to contain either of the previous two values, meaning that the next in the list is row 8 (C and D), it should then loop through the list to find the next unassigned row that doesn't contain either C or D which means the next row will be row 4 (A and E) it then goes back through the list to find the next unassigned row not containing the previous two letters which would be row 5 (B and C) and so on and so on.

Any help would be much appreciated!

Many thanks

Martin

[Marc L]

Hi,

join a workbook with source data and desired result …

Regards !

[woodsquash]

Will that still work if I have different numbers of combinations?

For example, if column A has 8 variables, then the combinations are 28 rows long and the solution will be different than other numbers of variables in column A?

I may just not be understanding what you've said?

Many thanks

Martin

[Marc L]

Attach a workbook file within source data and desired result !

[woodsquash]

Hi,

Attachment below

Hope this helps

M

[MrShorty]

I'm not very good with this kind of programming. It looks like your existing macro is creating the permutations in lexicographic order (a very common algorithm for generating permutations (see Wikipedia's article, which describes in basic terms common algorithms for generating permutations http://en.wikipedia.org/wiki/Permutation). Sorting this list into something other than standard lexicographic order looks challenging to me. If this were me, I would probably study the algorithm (might be more work than actually programming the algorithm) until I could see a different algorithm for generating the permutations in the desired order. Off the top of my head, this looks something like:

1) Start with earliest letter not in previous entry. (1s entry, this is A, 2nd entry this is C, 3rd entry this is A, and so on)
2) Identify the pairings with this letter that have already by used and the letters used in the previous entry. (2nd entry cannot use A and B, 3rd entry cannot use A, B, C, D, and so on)
3) After eliminating possibilities, current entry will be result of (1) and the earliest availabe letter not in (2).
4) Repeat loop for each entry.

I should probably encourage to do a little research and see if there's a formalized description of this kind of algorithm in the annals of computer science or whatever field this algorithm applies to (part of me has to believe you are not the first one to want to generate a list of permutations like this).

Wish I could help more.

[shg]

It's trivially not possible with three players.

With four players, you start with AB, then CD (per force), then you're done.

AB
CD
AC
AD
BC
BD

With five players, just noodling around,

AB
CD
AE
BC
AD
BE
AC
BD
CE
DE

That's not a proof, but I'm thinking it's not possible.

What's the purpose?

[Marc L]

woodsquash, the attached workbook's result doesn't match with the algorithm in your original post,
can you update the new one according with column Match No ?