Not sure if this SE site the best place to help find a solution for this problem. I am open to suggestions!
It basically boils down to this: Given a grid of numbers where the numbers are ordered sequentially across and down (typewriter-style), how do I get unique vertical pairs of numbers using a formula, so that applying the formula to any given number in the grid will provide it's paired number?
Some rules....
- There is always either an even number of columns or an even number of rows
- The minimum number of columns is 1.
- The minimum number of rows is 2.
A simple example would be a 6x2 grid:
The pairs here are simple- 1-7, 2-8, 3-9, 4-10, 5-11, and 6-12. If the function I am looking for was named myFunc, then in the simple 6x2 grid example above, myFunc(1) would return 7, and myFunc(7) would return 1. myFunc(4) would return 10, etc.
I'd like to have good distribution of the pairs across the rows, too. So if we add 2 more rows to the example above, the pairs might look like this (color-coded here):
The reds are pairs, like 1-13 and 2-20, and the greens are pairs, like 7-19 and 8-14. This is just a suggestion of course; in a different version of pairing, 1-7 and 13-19 could be pairs, and 2-8 and 14-20 could be pairs, etc.
I have come up with a formula in Excel which works for a 4-row grid of any number of columns. Unfortunately it is woefully inelegant and I wouldn’t even know how to best post it here. I can say it is a function of the MOD of the position number and row count, multiplied and added by the column count. And although it scales down to work with 2 rows, it does not scale to odd numbers of rows, or even 6 rows.
------ EDIT, 11 Hours Later ------
It would seem the "magic" of this lies in the calculated offset from the source number. Using the equation
1 + (ColNum mod (RowCount - 1))
it is easy to obtain the offset for the 1st row.
- Looking at the 1-13 pair, the offset is 2 (that is, the match is found 2 rows away from row 1).
- Looking at the 2-20 pair, the offset is 3.
- Looking at the 3-9 pair, the offset 1.
- The pattern continues for as many columns that reside in the grid.
So this 2,3,1 sequence also works for row 3 in a 4-row grid. We only need to do a little subtraction if the values exceed the maximum position in the grid. For me, it's easy to visualize if I drop a "phantom" grid below the first to show that match:
So here if we look at the 6-12 match, it’s easy to see the 12 is one row away from the 6. But we can also see that the 6 is 3 rows away from the 12 (count down into the phantom grid).
For the 2nd and 4th rows of a 4-row grid, the pattern of offsets is 2,1,3...
It is this 2,3,1 and 2,1,3 sequence that is the secret! I will refer to this 1,2, or 3 value as the "offset". So, I need to calculate the offset based on the following variables:
- RowCount (in this example, 4)
- ColCount (in this example, 6)
- RowNum (in this example, 1,2,3 or 4)
- ColNum (in this example, 1,2,3,4,5 or 6)
- Pos (in this example, 1 through 24)
I have found other sequences that work, but have been unable to calculate them. For a 6-row grid, the sequences are
Row1 - 2,3,4,5,1...
Row2 - 2,3,4,1,5...
Row3 - 4,3,1,5,2...
Row4 - 4,3,5,1,2...
Row5 - 5,3,2,1,4...
And as if this all weren't hairy enough (at least for me!) if we are dealing with an odd number of rows, a position's match may not be in the same column!
