Help with formula

[twiley23]

I was looking to see how to assign a word to certain numbers and make them random. Also the word would need to have an assigned # of
How many times it will show up.

For example:

# Friday Saturday Sunday

1
2
3
4
5
6
7
8
9
10
11

The word bacon will only show up 1 time on either the number 2 or 10 and be placed under Friday, Saturday or Sunday randomly

The word eggs will show up 2 times on either number 1 or 11 and be placed under Friday or Saturday or Sunday randomly

Thanks , please let me know if this is even possible.

 

[Snakehips]

@twiley23 I have used the Copy icon (to left of Column letter A) to copy then paste my posted XL2BB solution back to a fresh sheet and it is working perfectly for me. No issue with allocating bacon or anything else.
Did you copy/paste the XL2BB ? Are your data ranges exactly the same? Maybe take a look to double check the formulas you have in rows 3 and 10.

If you continue to have issue then please download XL2BB and post back with your mini sheet showing what you have.

 

[twiley23]

@twiley23 I have used the Copy icon (to left of Column letter A) to copy then paste my posted XL2BB solution back to a fresh sheet and it is working perfectly for me. No issue with allocating bacon or anything else.
Did you copy/paste the XL2BB ? Are your data ranges exactly the same? Maybe take a look to double check the formulas you have in rows 3 and 10.

If you continue to have issue then please download XL2BB and post back with your mini sheet showing what you have.

Awesome !
Thank you , I will try when I get home , can I message you with some follow up questions ? Thanks

 

[Snakehips]

Yes that's fine with me.

 

[Snakehips]

Having had an update from @twiley23 as to the ‘rules’ he is trying to follow, here is a revised solution.
It’s a bit of a mix of worksheet formulas in helper columns and vba.
The vba is primarily to fix the values of what would otherwise be a load of volatile randomising functions.
Helper columns G, K & M still contain various formulas. . These remaining formulas could also be populated via vba if absolutely necessary. Helper columns can be hidden if required.

Book999.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Dice #	1	2	3		Words	Random Row	Rand Between For Row	Row Option 1	Row Option 2	Random Column	Random # For Column	Ranked Random # For Column	Rand Between For Column	Dice #
2	2		BACON			BACON	2	1	2	2	2			2	only row 2
3	3	MUFFIN	TOAST	PANCAKES		BACON	12	2	12	12	3			3	only row 12
4	4		BUTTER			EGGS	7	2	7	7	2			2	only row 7
5	5
6	6					PANCAKES	3	1	3	11	3	0.20361042	3	2	3 or 11
7	7		EGGS			TOAST	3	1	3	11	2	0.757638419	2	2	3 or 11
8	8					MUFFIN	3	1	3	11	1	0.760053729	1	2	3 or 11
9	9											0.376682074	2
10	10	SYURP	HONEY	JAM		BUTTER	4	1	4	10	2	0.850218957	1		4 or 10
11	11					HONEY	10	2	4	10	2	0.210362178	3		4 or 10
12	12			BACON		JAM	10	2	4	10	3	0.208905933	2		4 or 10
13						SYURP	10	2	4	10	1	0.150851256	3		4 or 10
14												0.384362302	1
15						OJ	5	1	5	9	2			2	5 or 9
16
Sheet3

Cell Formulas
Range	Formula
G2:G4,G15,G10:G13,G6:G8	G2	=CHOOSE(H2,I2,J2)
K2:K4,K15	K2	=N2
K6:K8	K6	=IF(COUNTIF(G$6:G$8,G6)=1,N6,M6)
K10:K13	K10	=IF(G10=4,INDEX(M$9:M$11,COUNTIF(G$10:G10,G10)),INDEX(M$12:M$14,COUNTIF(G$10:G10,G10)))
M6:M8	M6	=RANK(L6,L$6:L$8,0)
M9:M11	M9	=RANK(L9,L$9:L$11)
M12:M14	M12	=RANK(L12,L$12:L$14)

Sub RandPicks()
Dim Rng1 As Range
Dim Rng2 As Range
Dim RowVal As Range

Application.ScreenUpdating = False

'refresh helper columns requiring use of 'Volatile' Ranomising functions . Then fix values

'set randbetween for row picks
Set Rng1 = Union(Range("H2:H4"), Range("H6:H8"), Range("H10:H12"), Range("H15"))
Rng1.Formula = "=RANDBETWEEN(1,2)"
Rng1.Value = Rng1.Value
'set random numbers option for column pick
Range("L6:L14").Formula = "=RAND()"
Range("L6:L14").Value = Range("L6:L14").Value
'Set randbetween option for column pick
Set Rng2 = Union(Range("N2:N4"), Range("N6:N8"), Range("N15"))
Rng2.Formula = "=RANDBETWEEN(1,3)"
Rng2.Value = Rng2.Value
'set formula to limit max of 3 words picked in either roe 4 or row 10
Range("H13").Formula = "=IF(SUM(H10:H12)=(3*H10),CHOOSE(H10,2,1),RANDBETWEEN(1,2))"
Range("H13").Value = Range("H13").Value

'alllocate words
Range("B2:D12").ClearContents

For Each RowVal In Range(" G2:G13")
    If Not IsEmpty(RowVal) Then
    Cells(RowVal.Value, RowVal.Offset(0, 4).Value + 1) = RowVal.Offset(0, -1).Value
    End If
Next RowVal

Application.ScreenUpdating = True

End Sub

HTH

 

[Snakehips]

@twiley23 !!! Just realised that the above code has a typo that will prevent the allocation of OJ

Edit the line For Each RowVal In Range(" G2:G13")

to read For Each RowVal In Range(" G2:G15")

 

[twiley23]

Awesome ! Thank you !! I will give this a shot when I’m home

 

[twiley23]

It worked perfectly ! Thank you for all your help

 

[Snakehips]

You are most welcome.