Juggler_IN
Active Member
- Joined
- Nov 19, 2014
- Messages
- 358
- Office Version
- 2003 or older
- Platform
- Windows
I am posting this in lieu of an unanswered thread -- "VBA to List all Possible Pairs of X,Y Data in two Columns"
I have two columns of X,Y data. I need to rearrange them so that I get all possible distinct pairs. That is,
Keep the X’s in the same order. There are 5 possible Y’s for the first position. For each of these, there are 4 possible Y’s for the second. For each of these there are 3 possible Y’s for the third, and 2 for the fourth. And, so on.
Once we are done with the first value of X, we go on to the next, and so on.
For Example,
The master pair data is:
X,Y
1,6
2,7
3,8
4,9
5,0
The first few distinct possibilities (of the 120 possible) done manually are:
1,7
2,8
3,9
4,0
5,6
1,8
2,9
3,0
4,6
5,7
and so on.
I want a VBA solution to this and handle X,Y data with more than 5 pairs.
The number of ways the X and Y numbers could be paired involves pairing change only (Note that X’s do not become Y’s and Y’s do not become X’s. It is the pairings that change). This gives N=5, thus FACT(5) = 120 possible X,Y data with pair changes. If N the FACT(N) possible X,Y data pairs possible.
HTML:
https://www.mrexcel.com/forum/excel-questions/1038311-vba-list-all-possible-pairs-x-y-data-two-columns.htmlI have two columns of X,Y data. I need to rearrange them so that I get all possible distinct pairs. That is,
Keep the X’s in the same order. There are 5 possible Y’s for the first position. For each of these, there are 4 possible Y’s for the second. For each of these there are 3 possible Y’s for the third, and 2 for the fourth. And, so on.
Once we are done with the first value of X, we go on to the next, and so on.
For Example,
The master pair data is:
X,Y
1,6
2,7
3,8
4,9
5,0
The first few distinct possibilities (of the 120 possible) done manually are:
1,7
2,8
3,9
4,0
5,6
1,8
2,9
3,0
4,6
5,7
and so on.
I want a VBA solution to this and handle X,Y data with more than 5 pairs.
The number of ways the X and Y numbers could be paired involves pairing change only (Note that X’s do not become Y’s and Y’s do not become X’s. It is the pairings that change). This gives N=5, thus FACT(5) = 120 possible X,Y data with pair changes. If N the FACT(N) possible X,Y data pairs possible.
