How do I generate random numbers for duplicate entries in Column A

[ddub25]

[

Book2
	A	B	C	D	E	F
1	Text String	Column2	Column1	Rand	Outcome 1	Outcome 2
2	Apple	1	1	1	1	1
3	Banana	1	1	1	1	1
4	Strawberry	2	#VALUE!	#VALUE!	2	1
5	Orange	1	1	1	1	1
6	Raspberry	3	#VALUE!	#VALUE!	3	2
7	Blackberry	1	1	1	1	1
8	Strawberry	2	#VALUE!	#VALUE!	1	2
9	Raspberry	3	#VALUE!	#VALUE!	1	3
10	Mango	1	1	1	1	1
11	Raspberry	3	#VALUE!	#VALUE!	2	1
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=COUNTIF([Text String], [@[Text String]])
C2:C11	C2	=IF(B2>1, RANK.EQ(A2, [Text String], 0) + RANDBETWEEN(0, B2-1), 1)
D2:D11	D2	=IF(B2>1, RANK.EQ(C2, [Column1], 0), 1)

In Column D I want to output random numbers based on entries in Column A.

If an entry in Column A is unique, only occurring once, then the corresponding row in Column D should return a '1'.

If an entry in Column A occurs more than once, as with 'Strawberry' (occurring 2 times), then in this example a random number of '1' or '2' should be returned in their corresponding rows.
Column E & F in the table gives two possible example outcomes that I would expect when the random numbers are regenerated. The COUNT of 'Strawberry' in Column A is 2, and therefore the random numbers in their rows of Column D should be a 1 or a 2. (Note: Both rows returning '2' or both returning '1' would be incorrect. They need to be unique.)

I have made attempts at a solution using Helper Columns in B and C, but it's not working.

Help would be greatly appreciated.

 

[etaf]

not random, but would be 1,2,3 based on count

=COUNTIF($A$2:A2,A2)

so it will be in order 1 then 2 then 3

 

[ddub25]

Thanks for your suggestion etaf, but I would like the random numbers to generate in no particular order. This sheet will be recalculated over and over again and so I would like it to throw up different random arrangements. Any further suggestions?

 

[Eric W]

This painful array formula should work for you in Excel 2016:

Book1
	A	B
1	Text String	Column3
2	Apple	1
3	Banana	1
4	Strawberry	2
5	Orange	1
6	Raspberry	3
7	Blackberry	1
8	Strawberry	1
9	Raspberry	1
10	Mango	1
11	Raspberry	2
Sheet3

Cell Formulas
Range	Formula
B2:B11	B2	=SMALL(IF(COUNTIFS(Table1[[#Headers],[Text String]]:OFFSET([@[Text String]],-1,0),[@[Text String]],Table1[[#Headers],[Column3]]:OFFSET([@Column3],-1,0),ROW(INDIRECT("1:"&COUNTIF([Text String],[@[Text String]])))),"",ROW(INDIRECT("1:"&COUNTIF([Text String],[@[Text String]])))),RANDBETWEEN(1,COUNTIF(OFFSET([@[Text String]],0,0,ROW(Table1[#Headers])+ROWS(Table1)-ROW([@Column3])+1),[@[Text String]])))
Press CTRL+SHIFT+ENTER to enter array formulas.

This actually highlights some drawbacks to table nomenclature, there's no good way to reference "row before", "row after", "last row of table", "first row", etc. If you can just enter the actual ranges, the formula looks like:

Book1
	A	B
1	Text String	Column3
2	Apple	1
3	Banana	1
4	Strawberry	2
5	Orange	1
6	Raspberry	3
7	Blackberry	1
8	Strawberry	1
9	Raspberry	2
10	Mango	1
11	Raspberry	1
Sheet4

Cell Formulas
Range	Formula
B2:B11	B2	=SMALL(IF(COUNTIFS(A$1:A1,A2,B$1:B1,ROW(INDIRECT("1:"&COUNTIF(A$2:A$11,A2)))),"",ROW(INDIRECT("1:"&COUNTIF(A$2:A$11,A2)))),RANDBETWEEN(1,COUNTIF(A2:A$11,A2)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

A somewhat shorter version of the table formula:

=SMALL(IF(COUNTIFS(Table1[[#Headers],[Text String]]:OFFSET([@[Text String]],-1,0),[@[Text String]],Table1[[#Headers],[Column3]]:OFFSET([@Column3],-1,0),ROW(INDIRECT("1:"&COUNTIF([Text String],[@[Text String]])))),"",ROW(INDIRECT("1:"&COUNTIF([Text String],[@[Text String]])))),RANDBETWEEN(1,COUNTIF([@[Text String]]:INDEX([Text String],ROWS(Table1)),[@[Text String]])))

 

[ddub25]

Thanks very much for your help Eric, I've used the first Table formula you suggested and it works great. For some reason the 2nd Table formula suggested doesn't return the correct results.

I have realised that my Excel version is now 2019 and have updated this in my profile. Does this version allow for a simpler formula?

 

[Eric W]

It's odd that the second formula doesn't work right, but as long as you have a working version, that's ok. If you have Excel 2021 or 365, you could replace the ROW(INDIRECT( part with the SEQUENCE function, and you wouldn't need the Control+Shift+Enter. But those are minor changes actually. Just for fun, I came up with a spill version of the formula which would work in a newer version:

Book1
	A	B
1	Text String	Column3
2	Apple	1
3	Banana	1
4	Strawberry	1
5	Orange	1
6	Raspberry	3
7	Blackberry	1
8	Strawberry	2
9	Raspberry	1
10	Mango	1
11	Raspberry	2
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=LET(text,A2:A11,r,ROWS(text),sumi,SORTBY(COUNTIF(OFFSET(text,0,0,SEQUENCE(r)),text),text),SORTBY(SORTBY(sumi,SORT(text),1,RANDARRAY(r),1),SORTBY(ROW(text),text)))
Dynamic array formulas.

Spill formulas only need to be entered in 1 cell, and the results will automatically fill down the column to the other rows. This uses some newer functions, but spill formulas don't work in tables, so it wouldn't help you anyway. (And it's still not especially easy to understand.) So bottom line, a newer version of Excel wouldn't really help with this formula.

Anyway, glad we could help!

 

[ddub25]

Thanks very much for your help Eric. I really appreciate it. I will look into upgrading to Excel 2021 anyway as I am sure there are other functions that I am missing out on.

 

[Eric W]

Rumor has it that Excel 2024 is coming out soon. If you don't have a real urgency, and you don't like the subscription model of 365, you might want to hold off a bit.