Distribute numbers in array

**safetboy** · 01-12-2016, 07:53 PM

This is an extension to a thread I posted earlier.

I want to sort numbers in an array that do not repeat in the same row or column. Also, the numbers in each row CANNOT be moved from that row, but CAN be moved from the column. Numbers in a column CANNOT repeat (for non-example, the number 0 is found in column one on rows 1, 8, 15, 22, 29, 36, 43, 50; for a good example, the number 49 is in different columns found in rows 1, 2, 3, 4, 5, 6, 7, 57). For your information, the numbers are listed below.

0 1 2 3 4 5 6 49
7 49 9 10 11 12 13 8
15 14 49 16 17 18 19 20
23 21 22 49 24 25 26 27
32 33 34 28 49 29 30 31
35 36 37 38 39 49 41 40
42 43 44 45 46 47 49 48
0 35 7 42 14 50 21 28
1 36 8 43 15 50 22 29
2 37 9 44 16 50 23 30
3 38 10 45 17 50 24 31
32 4 39 11 50 46 18 25
33 5 40 12 47 50 19 26
34 6 41 13 48 50 20 27
0 32 48 8 16 40 51 24
1 33 41 42 17 51 9 25
34 35 10 43 2 18 51 26
51 3 36 11 44 19 27 28
4 37 12 45 51 20 21 29
5 38 13 14 51 46 22 30
6 39 7 15 51 23 47 31
0 38 9 47 18 52 27 29
1 39 10 48 19 52 21 30
2 40 42 11 20 22 52 31
32 3 41 43 12 14 52 23
33 35 4 44 13 15 52 24
34 36 5 7 45 16 52 25
37 6 8 46 17 52 26 28
0 33 36 10 46 20 53 23
1 34 37 11 14 47 53 24
2 38 12 15 48 53 25 28
3 39 42 13 16 53 26 29
4 7 40 43 17 53 27 30
5 8 41 44 18 21 53 31
32 35 6 9 45 19 53 22
0 41 11 45 15 54 26 30
1 35 12 46 16 54 27 31
32 2 36 13 47 17 21 54
33 3 37 7 48 18 22 54
34 4 38 8 42 19 54 23
5 39 9 43 20 54 24 28
6 40 10 44 14 54 25 29
0 34 39 44 12 17 22 55
1 40 55 13 45 18 23 28
2 7 41 46 19 55 24 29
3 8 47 35 20 55 25 30
4 9 14 48 55 36 26 31
32 37 10 15 55 27 42 5
33 43 38 6 11 16 21 55
0 37 43 13 19 56 25 31
32 1 38 7 44 20 56 26
33 2 39 8 45 14 56 27
34 3 40 9 46 15 21 56
4 41 10 47 16 22 56 28
35 5 11 48 17 23 56 29
36 6 42 12 56 18 24 30
49 50 51 52 53 54 55 56

Is there a formula that can do this for me? Thanks in advance.

**shg** · 01-13-2016, 10:29 AM

Select the range of interest, paste

=RandLatin()-1

in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

0	3	2	1	4	A1:E5: {=RandLatin()-1}
2	4	1	3	0
4	1	0	2	3
3	2	4	0	1
1	0	3	4	2

Function RandLatin(Optional bVolatile As Boolean = False) As Long()
  ' shg 2013

  ' UDF only
  ' Requires adRandLong()

  ' Returns a random Latin square of size n with symbols 1 to n
  ' (by shuffling the symbols, then the rows, then the columns)
  ' to the calling range

  ' e.g., in A1:E5, {=RandLatin()}

  ' All such squares generated in this fashion are members (I think)
  ' of the same isotopy class, so it doesn't generate all possibilities.

  Dim aiInp()       As Long
  Dim aiOut()       As Long
  Dim aiRnd()       As Long
  Dim n             As Long
  Dim i             As Long
  Dim j             As Long

  If bVolatile Then Application.Volatile

  With Application.Caller
    n = IIf(.Rows.Count > .Columns.Count, .Rows.Count, .Columns.Count)
  End With

  ReDim aiInp(1 To n, 1 To n)
  ReDim aiOut(1 To n, 1 To n)

  ' shuffle the symbols
  aiRnd = aiRandLong(1, n)
  For i = 1 To n
    For j = 1 To n
      aiInp(i, j) = aiRnd(((i + j - 2) Mod n) + 1)
    Next j
  Next i

  ' shuffle the rows
  aiRnd = aiRandLong(1, n)
  For i = 1 To n
    For j = 1 To n
      aiOut(i, j) = aiInp(aiRnd(i), j)
    Next j
  Next i

  aiInp = aiOut

  ' shuffle the columns
  aiRnd = aiRandLong(1, n)
  For i = 1 To n
    For j = 1 To n
      aiOut(j, i) = aiInp(j, aiRnd(i))
    Next j
  Next i

  RandLatin = aiOut
End Function

Public Function aiRandLong(iMin As Long, _
                           iMax As Long, _
                           Optional ByVal n As Long = -1, _
                           Optional bVolatile As Boolean = False) As Long()
  ' shg 2008
  ' UDF or VBA

  ' Returns a 1-based array of n unique Longs between iMin and iMax inclusive

  Dim ai()          As Long     ' array of numbers iMin to iMax
  Dim i             As Long     ' index to ai

  If bVolatile Then Application.Volatile True

  If n < 0 Then n = iMax - iMin + 1
  If iMin > iMax Or n > (iMax - iMin + 1) Or n < 1 Then Exit Function

  ReDim ai(iMin To iMax)

  For i = iMin To iMax
    ai(i) = i
  Next i

  FYShuffle ai
  If n > -1 Then ReDim Preserve ai(iMin To iMin + n - 1)
  aiRandLong = ai
End Function

Sub FYShuffle(av As Variant)
  ' In-situ Fisher-Yates shuffle of 1D array av
  ' VBA only

  Dim iLB           As Long
  Dim iTop          As Long
  Dim vTmp          As Variant
  Dim iRnd          As Long

  iLB = LBound(av)
  iTop = UBound(av) - iLB + 1

  Do While iTop
    iRnd = Int(Rnd * iTop)
    iTop = iTop - 1
    vTmp = av(iTop + iLB)
    av(iTop + iLB) = av(iRnd + iLB)
    av(iRnd + iLB) = vTmp
  Loop
End Sub

**safetboy** · 01-13-2016, 08:06 PM

I must be a newb. Always returns #Name? Also, I've never used Macro: I copied and pasted your's by opening up Visual Basic Editor, selecting my sheet and then copying a pasting it. Should that do the trick?

The trick with my listed numbers is that in any given row only 1 number matches another number, hence why my numbers cannot move from their row. Just don't want the same number to appear in the same column, yet still retain that only 1 number matches another number in any given row. Hope that's clear.

Thanks thus far and for the prompt reply.

**shg** · 01-13-2016, 08:16 PM

The code needs to go in a standard code module. In the VBE, Insert > Module, and paste the code in the window that opens.

**safetboy** · 01-14-2016, 01:43 AM

I was beating my head silly and finally thought, "I should switch from my MacBook Pro to my PC". I did that: no problems and the Latin Rectangle performed flawlessly. Thank you and thank you for your patience.

However, applying a Latin Rectangle to my array still doesn't solve my problem and I think it is because there are eight quantities of each number. This condition must be met: the array must have only 1 number from any row to be the same number in any given row. If you look at my array in the original post, row 1 with numbers 0 1 2 3 4 5 6 49, has any one (and only one) of those numbers in the remaining 56 rows. The same can be said about any other number in any other row. The Latin Rectangle solves the problem that there are no repeats of any number within a column or row, but it randomizes the numbers such that I can no longer reach the condition. The same principle I'm trying to illustrate is found in the game "Spot It!" http://www.blueorangegames.com/spotit/

Thanks in advance. You've been helpful and I appreciate your support.

**shg** · 01-14-2016, 11:54 AM

Ah. Dunno, would have to ponder that.

**safetboy** · 01-29-2016, 01:53 AM

I finally took the time to do it manually and got it all here. A number never repeats in the same column or row and every row will have at least 1 matching number in any given row. Couldn't find a formula to work... hopefully someone can come up with something... Thanks for your help and interest.

25 2 1 3 4 0
9 7 6 25 8 5
12 13 14 11 25 10
17 25 18 19 15 16
22 21 25 20 23 24
5 10 26 0 20 15
6 1 11 16 21 26
2 22 12 7 26 17
8 3 23 26 18 13
26 14 19 4 24 9
24 6 0 18 12 27
7 20 27 13 1 19
27 8 15 2 14 21
16 27 9 10 22 3
11 17 5 23 27 4
23 0 7 28 16 14
28 24 17 8 10 1
18 11 20 9 28 2
19 12 21 5 3 28
4 28 22 15 13 6
29 19 8 22 0 11
15 23 29 1 9 12
13 5 16 24 2 29
14 29 3 17 6 20
21 4 10 29 7 18
0 9 13 21 17 30
1 18 30 14 5 22
10 30 2 6 19 23
3 15 24 30 11 7
20 16 4 12 30 8
30 26 28 27 29 25

Distribute numbers in array

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Distribute numbers in array

Re: Distribute numbers in array

Re: Distribute numbers in array

Re: Distribute numbers in array

Re: Distribute numbers in array

Re: Distribute numbers in array

Re: Distribute numbers in array

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Distribute given numbers in an array without repeating

Distribute numbers into buckets

Distribute Numbers

Distribute Whole Numbers Based on Percentages

Evenly distribute numbers

Distribute dates by week numbers

[SOLVED] Divide numbers and distribute evenly.

Bookmarks

Bookmarks

Posting Permissions