A few questions about randomizing arrays while avoiding doubles...

[kingsushi001]

Hi Everyone

I seem to be having problems and I just can't figure it out. Been googling a few days now without any clear answer.

I have this sheet:

ColorsExample.xlsx
	A	B	C	D
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX
2	Magenta
3	Green
4	Red
5	Yellow
6	Yellow
7	Blue
8	Green
9	Red
10	Yellow
11	Red
12	Blue
13	Green
14	Yellow
15	Yellow
16	Magenta
17	Magenta
18	Blue
19	Cyan
20	Red
main

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX(color,RANDBETWEEN(1,ROWS(color)),1)

The Colors in column A are pulled from an array (color) on this sheet:

ColorsExample.xlsx
	A	B
1	Colors	HEX
2	Blue	0000FF
3	Cyan	00FFFF
4	Green	00FF00
5	Magenta	FF00FF
6	Red	FF0000
7	Yellow	FFFF00
col

Now my problem is that I need the HEX corresponding to the color in A from the array to be in column B, but seeing as column A is random, the HEX column gets all messed up and I have no idea how to fix it.

My second problem is that it needs to be done for Primary Color/HEX AND Secondary Color/HEX, except that the values for Secondary Color cannot be the same as for Primary Color.
Meaning that if for instance A2 is Green, the value for C2 can't be green as well, it needs to either choose the next value in line, or a new random one.

If anyone can help me out with this, it would be greatly appreciated as I'm losing sleep over this ?

 

[jorismoerings]

Hi,

do not know for which excel version this needs to work. I suggest you update your account details and
mention the excel version.

in the meantime, try this with O365 version for secondary color.

=INDEX(FILTER(color, color<>A2),RANDBETWEEN(ROWS(FILTER(color,color<>A2))),1)

 

[Habtest]

Hi, is this what you are looking for?

TEST.xlsx
	A	B	C	D	E	F	G
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
2	Magenta	FF00FF	Green	00FF00		Blue	0000FF
3	Blue	0000FF	Magenta	FF00FF		Cyan	00FFFF
4	Cyan	00FFFF	Red	FF0000		Green	00FF00
5	Green	00FF00	Cyan	00FFFF		Magenta	FF00FF
6	Green	00FF00	Cyan	00FFFF		Red	FF0000
7	Red	FF0000	Blue	0000FF		Yellow	FFFF00
8	Magenta	FF00FF	Blue	0000FF
9	Red	FF0000	Cyan	00FFFF
10	Cyan	00FFFF	Green	00FF00
11	Red	FF0000	Blue	0000FF
12	Green	00FF00	Magenta	FF00FF
13	Green	00FF00	Cyan	00FFFF
14	Blue	0000FF	Red	FF0000
15	Magenta	FF00FF	Blue	0000FF
16	Green	00FF00	Red	FF0000
17	Magenta	FF00FF	Cyan	00FFFF
18	Cyan	00FFFF	Green	00FF00
19	Green	00FF00	Cyan	00FFFF
20	Blue	0000FF	Red	FF0000
Sheet1

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20	B2	=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20	C2	=INDEX($F$2:$F$7,AGGREGATE(15,6,ROW($1:$5)/(ROW($1:$5)<>MATCH(A2,$F$2:$F$7,0)),RANDBETWEEN(1,4)))

 

[kingsushi001]

Hi, is this what you are looking for?

TEST.xlsx
	A	B	C	D	E	F	G
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
2	Magenta	FF00FF	Green	00FF00		Blue	0000FF
3	Blue	0000FF	Magenta	FF00FF		Cyan	00FFFF
4	Cyan	00FFFF	Red	FF0000		Green	00FF00
5	Green	00FF00	Cyan	00FFFF		Magenta	FF00FF
6	Green	00FF00	Cyan	00FFFF		Red	FF0000
7	Red	FF0000	Blue	0000FF		Yellow	FFFF00
8	Magenta	FF00FF	Blue	0000FF
9	Red	FF0000	Cyan	00FFFF
10	Cyan	00FFFF	Green	00FF00
11	Red	FF0000	Blue	0000FF
12	Green	00FF00	Magenta	FF00FF
13	Green	00FF00	Cyan	00FFFF
14	Blue	0000FF	Red	FF0000
15	Magenta	FF00FF	Blue	0000FF
16	Green	00FF00	Red	FF0000
17	Magenta	FF00FF	Cyan	00FFFF
18	Cyan	00FFFF	Green	00FF00
19	Green	00FF00	Cyan	00FFFF
20	Blue	0000FF	Red	FF0000
Sheet1

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20	B2	=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20	C2	=INDEX($F$2:$F$7,AGGREGATE(15,6,ROW($1:$5)/(ROW($1:$5)<>MATCH(A2,$F$2:$F$7,0)),RANDBETWEEN(1,4)))

This is exactly what I was looking for. Thank you so much!

I do have a question though, why $1:$5 in the ROW function in AGGREGATE?

 

[kingsushi001]

Hi,

do not know for which excel version this needs to work. I suggest you update your account details and
mention the excel version.

in the meantime, try this with O365 version for secondary color.

=INDEX(FILTER(color, color<>A2),RANDBETWEEN(ROWS(FILTER(color,color<>A2))),1)

Thank you for this, unfortunately I don't have access to 365.

 

[Habtest]

This is exactly what I was looking for. Thank you so much!

I do have a question though, why $1:$5 in the ROW function in AGGREGATE?

Sorry it should be $1:$6 in the formula, otherwise yellow would never appear in secondary color. Correction:

=INDEX($F$2:$F$7,AGGREGATE(15,6,ROW($1:$6)/(ROW($1:$6)<>MATCH(A2,$F$2:$F$7,0)),RANDBETWEEN(1,5)))

ROW(1:6) produces an array of {1;2;3;4;5;6} that loops around 6 given colors, and helps to find primary color and eliminate it from the pool for randomizing.

 

[Peter_SSs]

Whilst the suggestion does do what you want, I would suggest a small change. It may never happen but if a user was to subsequently insert any new rows at the top of the sheet, say to add further headings, errors could appear.
For example:

21 08 22.xlsm
	A	B	C	D	E	F	G
1
2	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
3	Green	00FF00	#REF!	#REF!		Blue	0000FF
4	Blue	0000FF	Green	00FF00		Cyan	00FFFF
5	Cyan	00FFFF	Magenta	FF00FF		Green	00FF00
6	Blue	0000FF	Red	FF0000		Magenta	FF00FF
7	Blue	0000FF	Red	FF0000		Red	FF0000
8	Magenta	FF00FF	Yellow	FFFF00		Yellow	FFFF00
9	Cyan	00FFFF	#REF!	#REF!
10	Magenta	FF00FF	Green	00FF00
11	Red	FF0000	Magenta	FF00FF
12	Green	00FF00	Magenta	FF00FF
13	Blue	0000FF	Green	00FF00
14	Green	00FF00	Yellow	FFFF00
15	Cyan	00FFFF	#REF!	#REF!
16	Magenta	FF00FF	Cyan	00FFFF
17	Red	FF0000	Green	00FF00
18	Blue	0000FF	Cyan	00FFFF
19	Magenta	FF00FF	Green	00FF00
20	Blue	0000FF	Red	FF0000
21	Blue	0000FF	Magenta	FF00FF
Rand Colours

Cell Formulas
Range	Formula
A3:A21	A3	=INDEX($F$3:$F$8,RANDBETWEEN(1,5))
B3:B21,D3:D21	B3	=VLOOKUP(A3,$F$3:$G$8,2,0)
C3:C21	C3	=INDEX($F$3:$F$8,AGGREGATE(15,6,ROW($2:$7)/(ROW($2:$7)<>MATCH(A3,$F$3:$F$8,0)),RANDBETWEEN(1,5)))

The following adjustment to the formula for column C avoids that possibility (& is a tiny bit shorter anyway )

21 08 22.xlsm
	A	B	C	D	E	F	G
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
2	Green	00FF00	Magenta	FF00FF		Blue	0000FF
3	Red	FF0000	Cyan	00FFFF		Cyan	00FFFF
4	Blue	0000FF	Green	00FF00		Green	00FF00
5	Blue	0000FF	Yellow	FFFF00		Magenta	FF00FF
6	Red	FF0000	Cyan	00FFFF		Red	FF0000
7	Magenta	FF00FF	Green	00FF00		Yellow	FFFF00
8	Green	00FF00	Red	FF0000
9	Magenta	FF00FF	Green	00FF00
10	Cyan	00FFFF	Magenta	FF00FF
11	Cyan	00FFFF	Red	FF0000
12	Blue	0000FF	Green	00FF00
13	Cyan	00FFFF	Red	FF0000
14	Magenta	FF00FF	Blue	0000FF
15	Green	00FF00	Yellow	FFFF00
16	Cyan	00FFFF	Yellow	FFFF00
17	Green	00FF00	Cyan	00FFFF
18	Cyan	00FFFF	Blue	0000FF
19	Red	FF0000	Cyan	00FFFF
20	Green	00FF00	Magenta	FF00FF
Rand Colours (2)

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20	B2	=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20	C2	=INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$7)/(ROW(F$2:F$7)<>MATCH(A2,F:F,0)),RANDBETWEEN(1,5)))

 

[kingsushi001]

Whilst the suggestion does do what you want, I would suggest a small change. It may never happen but if a user was to subsequently insert any new rows at the top of the sheet, say to add further headings, errors could appear.
For example:

21 08 22.xlsm
	A	B	C	D	E	F	G
1
2	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
3	Green	00FF00	#REF!	#REF!		Blue	0000FF
4	Blue	0000FF	Green	00FF00		Cyan	00FFFF
5	Cyan	00FFFF	Magenta	FF00FF		Green	00FF00
6	Blue	0000FF	Red	FF0000		Magenta	FF00FF
7	Blue	0000FF	Red	FF0000		Red	FF0000
8	Magenta	FF00FF	Yellow	FFFF00		Yellow	FFFF00
9	Cyan	00FFFF	#REF!	#REF!
10	Magenta	FF00FF	Green	00FF00
11	Red	FF0000	Magenta	FF00FF
12	Green	00FF00	Magenta	FF00FF
13	Blue	0000FF	Green	00FF00
14	Green	00FF00	Yellow	FFFF00
15	Cyan	00FFFF	#REF!	#REF!
16	Magenta	FF00FF	Cyan	00FFFF
17	Red	FF0000	Green	00FF00
18	Blue	0000FF	Cyan	00FFFF
19	Magenta	FF00FF	Green	00FF00
20	Blue	0000FF	Red	FF0000
21	Blue	0000FF	Magenta	FF00FF
Rand Colours

Cell Formulas
Range	Formula
A3:A21	A3	=INDEX($F$3:$F$8,RANDBETWEEN(1,5))
B3:B21,D3:D21	B3	=VLOOKUP(A3,$F$3:$G$8,2,0)
C3:C21	C3	=INDEX($F$3:$F$8,AGGREGATE(15,6,ROW($2:$7)/(ROW($2:$7)<>MATCH(A3,$F$3:$F$8,0)),RANDBETWEEN(1,5)))

The following adjustment to the formula for column C avoids that possibility (& is a tiny bit shorter anyway )

21 08 22.xlsm
	A	B	C	D	E	F	G
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
2	Green	00FF00	Magenta	FF00FF		Blue	0000FF
3	Red	FF0000	Cyan	00FFFF		Cyan	00FFFF
4	Blue	0000FF	Green	00FF00		Green	00FF00
5	Blue	0000FF	Yellow	FFFF00		Magenta	FF00FF
6	Red	FF0000	Cyan	00FFFF		Red	FF0000
7	Magenta	FF00FF	Green	00FF00		Yellow	FFFF00
8	Green	00FF00	Red	FF0000
9	Magenta	FF00FF	Green	00FF00
10	Cyan	00FFFF	Magenta	FF00FF
11	Cyan	00FFFF	Red	FF0000
12	Blue	0000FF	Green	00FF00
13	Cyan	00FFFF	Red	FF0000
14	Magenta	FF00FF	Blue	0000FF
15	Green	00FF00	Yellow	FFFF00
16	Cyan	00FFFF	Yellow	FFFF00
17	Green	00FF00	Cyan	00FFFF
18	Cyan	00FFFF	Blue	0000FF
19	Red	FF0000	Cyan	00FFFF
20	Green	00FF00	Magenta	FF00FF
Rand Colours (2)

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20	B2	=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20	C2	=INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$7)/(ROW(F$2:F$7)<>MATCH(A2,F:F,0)),RANDBETWEEN(1,5)))

This is a good point, thank you. Fortunately I'll be the only one working with these sheets.

 

[kingsushi001]

Hi, is this what you are looking for?

TEST.xlsx
	A	B	C	D	E	F	G
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX		Colors	HEX
2	Magenta	FF00FF	Green	00FF00		Blue	0000FF
3	Blue	0000FF	Magenta	FF00FF		Cyan	00FFFF
4	Cyan	00FFFF	Red	FF0000		Green	00FF00
5	Green	00FF00	Cyan	00FFFF		Magenta	FF00FF
6	Green	00FF00	Cyan	00FFFF		Red	FF0000
7	Red	FF0000	Blue	0000FF		Yellow	FFFF00
8	Magenta	FF00FF	Blue	0000FF
9	Red	FF0000	Cyan	00FFFF
10	Cyan	00FFFF	Green	00FF00
11	Red	FF0000	Blue	0000FF
12	Green	00FF00	Magenta	FF00FF
13	Green	00FF00	Cyan	00FFFF
14	Blue	0000FF	Red	FF0000
15	Magenta	FF00FF	Blue	0000FF
16	Green	00FF00	Red	FF0000
17	Magenta	FF00FF	Cyan	00FFFF
18	Cyan	00FFFF	Green	00FF00
19	Green	00FF00	Cyan	00FFFF
20	Blue	0000FF	Red	FF0000
Sheet1

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20	B2	=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20	C2	=INDEX($F$2:$F$7,AGGREGATE(15,6,ROW($1:$5)/(ROW($1:$5)<>MATCH(A2,$F$2:$F$7,0)),RANDBETWEEN(1,4)))

I do have one last question. Is there any way for this to work with the original "color" array I had?
I feel that this would be the easiest way to update the sheet if I should remove or add more colors.

 

[Peter_SSs]

I do have one last question. Is there any way for this to work with the original "color" array I had?
I feel that this would be the easiest way to update the sheet if I should remove or add more colors.

I would suggest turning the lookup table on sheet 'col' into a formal Excel table (Insert tab -> Table) as shown below. I have renamed my table (done through the Formulas tab - Name Manager) to tblClrs

kingsushi001.xlsm
	A	B
1	Colors	HEX
2	Blue	0000FF
3	Cyan	00FFFF
4	Green	00FF00
5	Magenta	FF00FF
6	Red	FF0000
7	Yellow	FFFF00
8
col

Then you could use these in the 'main' tab.

kingsushi001.xlsm
	A	B	C	D
1	Primary Color	Primary HEX	Secondary Color	Secondary HEX
2	Green	00FF00	Cyan	00FFFF
3	Cyan	00FFFF	Blue	0000FF
4	Red	FF0000	Blue	0000FF
5	Yellow	FFFF00	Blue	0000FF
6	Yellow	FFFF00	Green	00FF00
7	Blue	0000FF	Magenta	FF00FF
8	Blue	0000FF	Magenta	FF00FF
9	Magenta	FF00FF	Cyan	00FFFF
10	Cyan	00FFFF	Magenta	FF00FF
11	Cyan	00FFFF	Blue	0000FF
12	Cyan	00FFFF	Magenta	FF00FF
13	Yellow	FFFF00	Magenta	FF00FF
14	Red	FF0000	Blue	0000FF
15	Magenta	FF00FF	Blue	0000FF
16	Cyan	00FFFF	Yellow	FFFF00
17	Green	00FF00	Red	FF0000
18	Magenta	FF00FF	Red	FF0000
19	Cyan	00FFFF	Red	FF0000
20	Magenta	FF00FF	Cyan	00FFFF
main

Cell Formulas
Range	Formula
A2:A20	A2	=INDEX(tblClrs[Colors],RANDBETWEEN(1,ROWS(tblClrs[Colors])))
B2:B20,D2:D20	B2	=VLOOKUP(A2,tblClrs,2,0)
C2:C20	C2	=INDEX(tblClrs[Colors],AGGREGATE(15,6,(ROW(tblClrs[Colors])-ROW(tblClrs[#Headers]))/(ROW(tblClrs[Colors])-ROW(tblClrs[#Headers])<>MATCH(A2,tblClrs[Colors],0)),RANDBETWEEN(1,ROWS(tblClrs[Colors])-1)))