Distribute given numbers in an array without repeating

**safetboy** · 01-08-2016, 02:48 AM

Hello all, first n00b post.

I am wishing to distribute numbers in 8 columns without any number repeating itself within the same column (for example, 32 is found in the first column seven times and I want it and all numbers to be distributed once into every column). It is imperative that the numbers do not move into a different row, but they may move within their own row. There are eight quantities of each number from 0 to 56.

What formula(s)/function(s) could I do in order to make this work?

Sample of what needs to be done:

49 1 2 3 4 5 6 0
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
56 50 51 52 53 54 55 49

Thanks in advance.

**jewelsharma** · 01-08-2016, 07:26 AM

that appears to be an iterative problem, so i doubt if there can be a formula/function to answer this; and with over 400 variable cells this is probably beyond the capabilities of EXCEL SOLVER as well. I'm keen to learn if there's a solution to this.

**safetboy** · 01-08-2016, 10:59 AM

Agreed about the iteration. It is likely done quite easily in some programming such as C++, Java, Python, etc. Simply hoping there's a way for it to be done within Excel so I can repeat again later.

**ChemistB** · 01-08-2016, 11:12 AM

In sheet2 A1 copied across to H1 and down to row 57

=RAND()

In Sheet1 A1 copied across and down
=MATCH(SMALL(Sheet2!A$1:A$57,ROWS($A$1:$A1)),Sheet2!A$1:A$57,0)-1

If you want to lock the values in place, then copy > paste special > values to remove the formulas (or to remove the =RAND() on sheet 2

**shg** · 01-08-2016, 11:15 AM

You mean like a random Latin rectangle? In the example below, the numbers 0 to 56 appear once in each column, and there are no duplicates in any row.

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

**ChemistB** · 01-08-2016, 11:17 AM

I never heard of a random Latin rectangle. Are they common in Texas?

**shg** · 01-08-2016, 11:20 AM

You bet -- Saturday nights in border towns, CB.

**ChemistB** · 01-08-2016, 11:24 AM

Ahhh, I've seen the signs "Line dancing and Latin Triangles, Saturday only!"

**shg** · 01-08-2016, 11:27 AM

Marty Robbins wrote a very moving song about a Latin triangle.

**ChemistB** · 01-08-2016, 11:34 AM

Who?

**shg** · 01-08-2016, 11:48 AM

Kids these days ...

**ChemistB** · 01-08-2016, 01:39 PM

I'm from NJ. We only listen to Bruce Springsteen and Bon Jovi.

**safetboy** · 01-08-2016, 02:14 PM

I've been a member for less than 12 hours and already have been provided a solution AND a formula. Thank you very much, ChemistB and shg! In my opinion this is solved, is there a way for me to mark it or does an Admin do that?

**ChemistB** · 01-08-2016, 02:22 PM

Above your first post there is a dropdown menu called "Thread Tools"
"Mark as solved" is on that dropdown.
Glad we could help.

**safetboy** · 01-08-2016, 05:49 PM

Originally Posted by ChemistB

In sheet2 A1 copied across to H1 and down to row 57

=RAND()

In Sheet1 A1 copied across and down
=MATCH(SMALL(Sheet2!A$1:A$57,ROWS($A$1:$A1)),Sheet2!A$1:A$57,0)-1

If you want to lock the values in place, then copy > paste special > values to remove the formulas (or to remove the =RAND() on sheet 2

I made an omission which shg picked up on. His Latin rectangle never has a number that repeats within the same column OR Row. Unfortunately, the formula you give never has a duplicate number within the same column, but will sometimes have a duplicate number within the same row. In your attachment, Row 24 has a duplicate number of 56 at D24 & E24. What needs to be added to the formula in order to avoid duplicates within the same row? Thanks in advance.

Distribute given numbers in an array without repeating

LinkBack

Thread Tools

Rate This Thread

Display

Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Re: Distribute given numbers in an array without repeating

Thread Information

Users Browsing this Thread

Similar Threads

Distribute numbers into buckets

Distribute Numbers

Distribute Whole Numbers Based on Percentages

Evenly Distribute Groups of Numbers?

Evenly distribute numbers

Distribute dates by week numbers

[SOLVED] Divide numbers and distribute evenly.

Bookmarks

Bookmarks

Posting Permissions