Formula help; Randomly display names from list no duplicates until you have to

[Tardisgx]

In the following context when I say randomly it means in a random order so every recalculate could move the results around.

In this example I am trying to randomly display these 3 colours without duplicating, e.g no red, red, red or red blue red

=INDEX(Table16[colours],MATCH(SMALL([Column1],ROW()-1),[Column1],0))

The above formula only works for 3 rows then gets a ref error.

What I need is for the output to behave exactly like it is doing and repeat itself for the rows thereafter.

So if I had 3 colours and there were 6 rows to populate I would want each colour to appear twice exactly but randomly.

If I had 3 colours and there were 12 rows to populate I would want each colour to appear 4 times exactly but randomly.

If I had 3 colours and there were 13 rows to populate I would want 2 colours to appear 4 times and 1 to appear 5.

If I had 3 colours and there were 2 rows to populate I would want 2 colours to appear once each.

 

[Tardisgx]

I figure the easiest way is to copy the table (with formulas) without headers and put it under the original table. That'll be random and fair.

Then I can vlookup results on based on row number.

Think I'll write some VBA to automate copying the table so I don't have to do that manually.

 

[Bo_Ry]

Book1
	D	E	F	G
1	Colors			Data
2	blue			blue
3	red			green
4	green			red
5				green
6				blue
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(Table1[Colors],AGGREGATE(15,6,ROW(Table1)/(COUNTIFS(G$1:G1,Table1[Colors])<INT((ROWS(G$2:G2)-1)/ROWS(Table1)+1)),RANDBETWEEN(1,ROWS(Table1)-MOD(ROWS(G$2:G2)-1,ROWS(Table1))))-ROW(Table1[#Headers]))

 

[Tardisgx]

Very Cool, thank you.