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.

 

[Kaper]

Hi,

What Excel version are you using?

It would be probably a bit easier to generate in Excel 365. But having only limited rows allowed, different for each word would make life harder.

In real problem - how many rows, how many columns, and how many different values (and with what count) is expected? May be the solution could be found with Solver?

 

[twiley23]

Hi,

What Excel version are you using?

It would be probably a bit easier to generate in Excel 365. But having only limited rows allowed, different for each word would make life harder.

In real problem - how many rows, how many columns, and how many different values (and with what count) is expected? May be the solution could be found with Solver?

My excel program is from 2013.

I have 11 rows and 3 columns.

There are a total of 11 words. Only 2 are duplicates. I can take a picture of what it will look like in the end result I’m going for. Thanks

 

[kvsrinivasamurthy]

It means that all 11 words are to be selected each time and there will be duplicates.

 

[Peter_SSs]

My excel program is from 2013.

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Snakehips]

@twiley23 It will help the cause if you can clarify define / illustrate all the rules which apply.
Questions:
Is it essentially, having observed any row restrictions, one word randomly attributed to each row, in a random pick of the 3 columns?
9 discreet words of which 2 are duplicated to make 11 ?
Two of those words are restricted in respect of rows, as per your original post?
Does 'Eggs' appear once in row 1 and once in row 11 or could it be twice in one row and not in the other?
Are there any other words that have restrictions?

 

[Snakehips]

Based upon what I think I understand so far the vba code below will refresh and fix random values in the yellow cells.
Formulas in B2:E12 then pick the words from F

Sub RandPicks()

Application.ScreenUpdating = False
Range("G2:G9").Formula = "=RAND()"
Range("H10").Formula = "=CHOOSE(RANDBETWEEN(1,2),2,10)"
Range("I2:I12").Formula = "=RANDBETWEEN(1,3)"
Range("H2:H9").Formula = "=RANK(G2,G$2:G$10)+1"

Range("G2:I12").Value = Range("G2:I12").Value
Application.ScreenUpdating = True

End Sub

TestTime.xlsm
	A	B	C	D	E	F	G	H	I
1	#	Friday	Saturday	Sunday		Words	Random#	Random Row	Random Column
2	1			Eggs		WordA	0.47549142	5	3
3	2	Bacon				WordB	0.17919249	8	2
4	3	WordF				WordC	0.07852914	9	1
5	4			WordE		WordD	0.25860006	7	3
6	5		WordA			WordE	0.49867189	4	2
7	6	OtherDup				WordF	0.55396281	3	1
8	7	WordD				OtherDup	0.80920072	2	1
9	8			WordB		OtherDup	0.29271408	6	3
10	9	WordC				Bacon		2	1
11	10	OtherDup				Eggs			2
12	11		Eggs			Eggs			2
RandPicks

Cell Formulas
Range	Formula
B2:D2	B2	=IF($I2=COLUMNS($B:B),$F11,"")
B3:D3	B3	=IF($I10<>COLUMNS($B:B),"",IF($H10=2,$F10,INDEX($F$2:$F$9,MATCH(2,$H$2:$H$9,0))))
B4:D10	B4	=IF($I4<>COLUMNS($B:B),"",INDEX($F$2:$F$9,MATCH($A4,$H$2:$H$9,0)))
B11:D11	B11	=IF($I10<>COLUMNS($B:B),"",IF($H10=10,$F10,INDEX($F$2:$F$9,MATCH(2,$H$2:$H$9,0))))
B12:D12	B12	=IF($I12=COLUMNS($B:B),$F12,"")

 

[Kaper]

I think that solution shall also allow only specific rows for given words. Going back to post #1:
word bacon will only show up 1 time on either the number 2 or 10 ....
word eggs will show up 2 times on either number 1 or 11 (so I assume eggs could not appear in row 2, or any other between 3 and 10).

 

[Snakehips]

I think that solution shall also allow only specific rows for given words. Going back to post #1:
word bacon will only show up 1 time on either the number 2 or 10 ....
word eggs will show up 2 times on either number 1 or 11 (so I assume eggs could not appear in row 2, or any other between 3 and 10).

@Kaper Yes. the column of each row is random.
Words 10 and 11 (Eggs) will be in both rows 1 and 11
Word 9 (Bacon) will randomly be in either row 2 or row 10.
Depending upon which row contains Bacon, all other words will be randomly assigned to either rows 2:9 or 3:10

 

[twiley23]

@Kaper Yes. the column of each row is random.
Words 10 and 11 (Eggs) will be in both rows 1 and 11
Word 9 (Bacon) will randomly be in either row 2 or row 10.
Depending upon which row contains Bacon, all other words will be randomly assigned to either rows 2:9 or 3:10

Hey! Thank you so much ! this is what I was looking for. The only issue I'm running into is for the "Bacon" when it assigns to row 3 or 11 it shows N/a on the opposite row. How do I also have it choose between the 2 rows. Thank you for your help.