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 ?

 

[kingsushi001]

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)))

This is exactly what I needed. I see the table method works way faster than the array method.

Thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.