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

kingsushi001

New Member
Joined
Oct 24, 2017
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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
ABCD
1Primary ColorPrimary HEXSecondary ColorSecondary HEX
2Magenta
3Green
4Red
5Yellow
6Yellow
7Blue
8Green
9Red
10Yellow
11Red
12Blue
13Green
14Yellow
15Yellow
16Magenta
17Magenta
18Blue
19Cyan
20Red
main
Cell Formulas
RangeFormula
A2:A20A2=INDEX(color,RANDBETWEEN(1,ROWS(color)),1)


The Colors in column A are pulled from an array (color) on this sheet:
ColorsExample.xlsx
AB
1ColorsHEX
2Blue0000FF
3Cyan00FFFF
4Green00FF00
5MagentaFF00FF
6RedFF0000
7YellowFFFF00
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 ?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.

Excel Formula:
=INDEX(FILTER(color, color<>A2),RANDBETWEEN(ROWS(FILTER(color,color<>A2))),1)
 
Upvote 0
Hi, is this what you are looking for?
Cell Formulas
RangeFormula
A2:A20A2=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20B2=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20C2=INDEX($F$2:$F$7,AGGREGATE(15,6,ROW($1:$5)/(ROW($1:$5)<>MATCH(A2,$F$2:$F$7,0)),RANDBETWEEN(1,4)))
 
Upvote 0
Hi, is this what you are looking for?
Cell Formulas
RangeFormula
A2:A20A2=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20B2=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20C2=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?
 
Upvote 0
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.

Excel Formula:
=INDEX(FILTER(color, color<>A2),RANDBETWEEN(ROWS(FILTER(color,color<>A2))),1)
Thank you for this, unfortunately I don't have access to 365.
 
Upvote 0
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:
Excel Formula:
=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.
 
Upvote 0
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:

Cell Formulas
RangeFormula
A3:A21A3=INDEX($F$3:$F$8,RANDBETWEEN(1,5))
B3:B21,D3:D21B3=VLOOKUP(A3,$F$3:$G$8,2,0)
C3:C21C3=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 :))

Cell Formulas
RangeFormula
A2:A20A2=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20B2=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20C2=INDEX(F:F,AGGREGATE(15,6,ROW(F$2:F$7)/(ROW(F$2:F$7)<>MATCH(A2,F:F,0)),RANDBETWEEN(1,5)))
 
Upvote 0
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:

Cell Formulas
RangeFormula
A3:A21A3=INDEX($F$3:$F$8,RANDBETWEEN(1,5))
B3:B21,D3:D21B3=VLOOKUP(A3,$F$3:$G$8,2,0)
C3:C21C3=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 :))

Cell Formulas
RangeFormula
A2:A20A2=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20B2=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20C2=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.
 
Upvote 0
Hi, is this what you are looking for?
Cell Formulas
RangeFormula
A2:A20A2=INDEX($F$2:$F$7,RANDBETWEEN(1,5))
B2:B20,D2:D20B2=VLOOKUP(A2,$F$2:$G$7,2,0)
C2:C20C2=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.
 
Upvote 0
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
AB
1ColorsHEX
2Blue0000FF
3Cyan00FFFF
4Green00FF00
5MagentaFF00FF
6RedFF0000
7YellowFFFF00
8
col


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

Cell Formulas
RangeFormula
A2:A20A2=INDEX(tblClrs[Colors],RANDBETWEEN(1,ROWS(tblClrs[Colors])))
B2:B20,D2:D20B2=VLOOKUP(A2,tblClrs,2,0)
C2:C20C2=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)))
 
Upvote 0
Solution

Forum statistics

Threads
1,223,929
Messages
6,175,460
Members
452,644
Latest member
gjcase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top