Generate random numbers and highlight those numbers in spreadsheet.

[Johncobb]

In my spreadsheet I have many rows of 40 numbers in each row (numbers between 1 and 80 in each row).
I want to generate 20 random numbers between 1 and 80 and then to check if they come up in each row (each row has 40 numbers between 1 and 80).
The numbers that came up should be highlighted in each row in a color so I will be able to see which numbers and how many numbers did come up after each random spin.
I hope that you understand what I mean. This is a short description. Please contact me if you need more explanations.
Waiting for comments and advice.
John

 

[Johncobb]

Is this what you want.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1	80	67	56	36	41	42	40	62	55	2	42	4	54	75	33	7	21	53	9	52	47	43	75	28	33	4	12	30	58	28	73	43	52	69	16	38	19	57	55	35	43	13	60	53	62	20	80	21	17	70	50	21	38	28	8	25	68	68	62	21
2	71	29	80	64	21	72	34	50	37	39	1	7	70	36	34	21	13	79	76	42	29	14	41	24	2	35	22	53	21	31	80	30	60	5	63	47	70	33	65	73	77	50	36	63	36	39	28	1	78	36	25	8	13	7	30	64	22	75	42	19
3	78	18	21	1	69	54	48	44	44	19	39	39	6	13	12	76	71	56	44	42	78	74	25	74	79	16	24	62	35	50	43	33	50	33	60	5	15	5	61	27	71	19	27	57	71	72	20	70	62	46	80	52	39	72	59	58	11	33	50	14
4	16	80	51	78	35	63	74	28	52	48	9	11	21	39	40	68	22	11	64	35	68	34	6	10	64	25	27	53	36	15	70	9	16	4	32	79	42	6	18	40	60	68	71	30	65	66	1	48	80	6	26	33	9	14	73	29	35	19	20	3
5	6	41	70	67	17	16	15	78	76	6	40	68	45	69	45	1	3	37	10	23	53	25	48	23	37	68	70	31	21	77	15	66	41	32	18	79	79	24	64	24	10	45	70	9	68	53	44	45	73	36	3	21	6	34	21	12	60	37	63	63
6	73	72	52	39	55	35	42	24	77	16	21	77	26	31	40	2	46	55	61	25	46	22	12	23	12	22	64	52	24	42	42	71	58	3	76	75	55	63	13	74	47	46	40	22	13	7	1	46	64	51	21	46	31	76	2	45	37	19	41	50
7	11	20	6	43	27	58	4	4	26	36	28	54	50	5	70	47	59	11	11	24	75	9	60	50	75	38	57	73	29	34	80	29	39	63	65	55	69	31	39	11	53	34	50	45	7	60	9	59	44	46	15	8	58	51	15	58	22	40	41	19
8	62	45	79	36	80	72	6	57	62	11	70	16	36	17	19	4	35	33	29	63	43	22	27	61	35	33	5	67	43	35	47	25	28	15	20	52	29	51	43	80	65	61	57	63	53	8	25	20	22	54	53	14	10	1	48	61	26	55	20	55
9	60	41	19	49	68	68	69	5	59	19	70	60	6	47	32	59	71	41	77	4	61	37	54	35	72	39	43	44	9	51	21	11	16	60	35	71	66	59	4	32	60	29	75	80	13	56	10	72	18	1	27	65	57	49	31	43	54	1	16	25
10	61	49	49	30	79	64	35	52	56	63	75	36	32	69	78	13	50	71	76	80	25	10	62	12	25	24	44	2	65	18	79	67	49	73	55	38	5	38	46	61	21	79	2	32	52	44	42	70	55	36	49	11	75	53	23	69	55	73	6	6
11	25	36	39	3	70	37	76	44	19	75	67	22	52	9	9	17	33	11	31	29	12	52	37	68	36	20	22	1	54	47	77	21	71	48	38	9	38	66	72	57	25	63	61	42	52	76	38	3	60	32	50	53	15	22	2	67	33	48	47	73
12	1	67	44	35	60	78	62	71	40	6	60	2	50	48	25	19	43	53	71	4	47	46	45	29	51	20	72	73	72	32	1	1	38	76	4	56	24	23	3	61	11	4	18	9	76	33	55	27	58	66	24	70	76	38	16	12	5	45	74	37
13	4	6	73	64	38	39	21	63	64	28	55	9	43	66	80	5	6	31	64	28	68	6	17	76	41	53	78	26	28	6	26	17	27	3	74	5	46	30	28	73	75	30	54	72	22	18	13	61	1	55	10	7	42	17	56	15	42	21	33	19
14	48	57	76	27	15	37	54	58	70	39	61	38	78	43	72	6	36	72	31	59	46	46	67	4	47	69	21	2	70	59	49	17	66	29	26	16	46	10	61	34	1	4	19	38	75	57	49	44	2	13	68	3	43	34	9	14	35	70	19	20
15	77	34	54	7	11	6	66	31	21	25	72	9	37	19	18	7	78	37	21	77	50	55	59	57	67	72	74	50	60	33	9	28	72	71	31	61	22	28	67	22	3	37	75	75	58	48	67	47	44	66	76	64	61	12	61	6	35	70	29	28
16	3	30	13	46	34	65	64	50	6	74	65	71	62	7	33	24	77	13	75	42	14	62	72	56	73	34	27	5	46	3	57	74	37	44	24	19	72	31	8	36	38	73	46	64	3	14	25	10	67	1	72	13	18	80	57	21	62	32	52	56
17	44	65	56	37	10	43	22	62	26	70	6	14	67	63	43	24	24	51	25	80	70	5	28	37	50	78	17	42	61	8	70	30	38	62	28	32	66	44	60	15	64	79	59	32	39	24	43	36	42	16	32	64	18	49	33	6	72	44	76	15
18	53	38	60	3	28	49	74	2	39	4	63	68	3	68	21	66	36	69	12	22	66	11	66	30	3	52	41	73	62	20	6	18	30	15	58	37	33	22	51	61	30	28	80	31	13	63	78	71	77	54	16	38	27	78	47	56	67	30	49	5
19	62	3	10	73	56	59	14	36	26	17	75	2	16	63	35	75	27	79	55	23	44	76	4	16	26	21	57	8	65	30	40	5	2	60	42	41	53	18	46	14	8	10	76	45	38	15	47	35	70	57	25	1	44	30	62	9	56	78	65	62
20	21	74	62	29	4	31	19	56	51	71	46	8	55	68	71	58	47	14	37	10	19	2	35	20	23	11	79	30	19	46	33	44	63	40	54	7	42	48	66	60	8	59	7	70	76	7	33	32	35	41	2	51	13	72	28	1	26	68	20	35
Main

Cell Formulas
Range	Formula
AO1:BH20	AO1	=RANDARRAY(20,20,1,80,1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AN20	Expression	=COUNTIFS(A1,$AO1:$BH1)	text	NO

No.
I have rows with numbers, 40 numbers in each row - range A1:AN291
I want to generate one set of 20 random numbers. - In my old project it was done in range AO1:AO20 with a formula =INT(RAND()*(80-1)+1).
I want a function or script to search each row and show me how many numbers did come up from those 20 randomly generated. Those numbers that match must be highlighted.
I hope that I explained correctly this time.

 

[Johncobb]

The 20 random numbers that you want to generate... will the same 20 numbers be highlighted throughout your table of numbers or do you want 20 random numbers generated and highlighted for each row individually?

the same 20 numbers be highlighted throughout the table of numbers
Thank you.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	80	67	56	36	41	42	40	62	55	2	42	4	54	75	33	7	21	53	9	52	47	43	75	28	33	4	12	30	58	28	73	43	52	69	16	38	19	57	55	35	59
2	71	29	80	64	21	72	34	50	37	39	1	7	70	36	34	21	13	79	76	42	29	14	41	24	2	35	22	53	21	31	80	30	60	5	63	47	70	33	65	73	54
3	78	18	21	1	69	54	48	44	44	19	39	39	6	13	12	76	71	56	44	42	78	74	25	74	79	16	24	62	35	50	43	33	50	33	60	5	15	5	61	27	38
4	16	80	51	78	35	63	74	28	52	48	9	11	21	39	40	68	22	11	64	35	68	34	6	10	64	25	27	53	36	15	70	9	16	4	32	79	42	6	18	40	17
5	6	41	70	67	17	16	15	78	76	6	40	68	45	69	45	1	3	37	10	23	53	25	48	23	37	68	70	31	21	77	15	66	41	32	18	79	79	24	64	24	10
6	73	72	52	39	55	35	42	24	77	16	21	77	26	31	40	2	46	55	61	25	46	22	12	23	12	22	64	52	24	42	42	71	58	3	76	75	55	63	13	74	25
7	11	20	6	43	27	58	4	4	26	36	28	54	50	5	70	47	59	11	11	24	75	9	60	50	75	38	57	73	29	34	80	29	39	63	65	55	69	31	39	11	16
8	62	45	79	36	80	72	6	57	62	11	70	16	36	17	19	4	35	33	29	63	43	22	27	61	35	33	5	67	43	35	47	25	28	15	20	52	29	51	43	80	71
9	60	41	19	49	68	68	69	5	59	19	70	60	6	47	32	59	71	41	77	4	61	37	54	35	72	39	43	44	9	51	21	11	16	60	35	71	66	59	4	32	77
10	61	49	49	30	79	64	35	52	56	63	75	36	32	69	78	13	50	71	76	80	25	10	62	12	25	24	44	2	65	18	79	67	49	73	55	38	5	38	46	61	78
11	25	36	39	3	70	37	76	44	19	75	67	22	52	9	9	17	33	11	31	29	12	52	37	68	36	20	22	1	54	47	77	21	71	48	38	9	38	66	72	57	32
12	1	67	44	35	60	78	62	71	40	6	60	2	50	48	25	19	43	53	71	4	47	46	45	29	51	20	72	73	72	32	1	1	38	76	4	56	24	23	3	61	58
13	4	6	73	64	38	39	21	63	64	28	55	9	43	66	80	5	6	31	64	28	68	6	17	76	41	53	78	26	28	6	26	17	27	3	74	5	46	30	28	73	6
14	48	57	76	27	15	37	54	58	70	39	61	38	78	43	72	6	36	72	31	59	46	46	67	4	47	69	21	2	70	59	49	17	66	29	26	16	46	10	61	34	52
15	77	34	54	7	11	6	66	31	21	25	72	9	37	19	18	7	78	37	21	77	50	55	59	57	67	72	74	50	60	33	9	28	72	71	31	61	22	28	67	22	23
16	3	30	13	46	34	65	64	50	6	74	65	71	62	7	33	24	77	13	75	42	14	62	72	56	73	34	27	5	46	3	57	74	37	44	24	19	72	31	8	36	23
17	44	65	56	37	10	43	22	62	26	70	6	14	67	63	43	24	24	51	25	80	70	5	28	37	50	78	17	42	61	8	70	30	38	62	28	32	66	44	60	15	74
18	53	38	60	3	28	49	74	2	39	4	63	68	3	68	21	66	36	69	12	22	66	11	66	30	3	52	41	73	62	20	6	18	30	15	58	37	33	22	51	61	15
19	62	3	10	73	56	59	14	36	26	17	75	2	16	63	35	75	27	79	55	23	44	76	4	16	26	21	57	8	65	30	40	5	2	60	42	41	53	18	46	14	69
20	21	74	62	29	4	31	19	56	51	71	46	8	55	68	71	58	47	14	37	10	19	2	35	20	23	11	79	30	19	46	33	44	63	40	54	7	42	48	66	60	67
Main

Cell Formulas
Range	Formula
AO1:AO20	AO1	=RANDARRAY(20,,1,80,1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AN20	Expression	=COUNTIFS($AO$1:$AO$20,A1)	text	NO

 

[Joe4]

Under you current conditions, the VBA code adjustment to my original code would look like:

Sub PopulateRandomNum()
   
    Dim rng As Range
    Dim rn As Long
    Dim cell As Range
    Dim ct As Long
   
'   Designate range to populate values in
    Set rng = Range("AO1:AO20")
   
'   Loop through each cell in range
    For Each cell In rng
        ct = 0
'       Populate next cell
        Do
'           Calculate random number
            rn = CreateRandomNum(1, 80)
'           Check to see if it is not found in range
            If Application.WorksheetFunction.CountIf(rng, rn) = 0 Then
'               Populate cell with random number
                cell.Value = rn
'               Exit loop
                Exit Do
            End If
'           Fail-safe to make sure you do not get caught in endless loop
            ct = ct + 1
            If ct = 200 Then
                MsgBox "Process failed and quit after 200 attempts.", vbOKOnly, "ERROR!"
                Exit Do
            End If
        Loop
    Next cell

End Sub


Function CreateRandomNum(lb As Long, ub As Long) As Long
    Randomize
    CreateRandomNum = Int((ub - lb + 1) * Rnd + lb)
End Function

And the Conditional Formatting rule would be the same one that Fluff posted.

So you should now have both VBA and non-VBA solutions to your problem.

 

[Rick Rothstein]

Here is another macro that you can try (uses only a single loop)...

Sub Pick20RandNumsAndHighlightThem()
  Dim Cnt As Long, RandomIndex As Long, RndNums As Variant
  RndNums = [ROW(1:80)]
  Range("AO1").Resize(, 20).Clear
  Cells.Interior.ColorIndex = 0
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbYellow
  For Cnt = 1 To 20
    RandomIndex = Application.RandBetween(Cnt, 80)
    Cells.Replace RndNums(RandomIndex, 1), "", xlWhole, , , , False, True
    Range("AN1").Offset(, Cnt) = RndNums(RandomIndex, 1)
    RndNums(RandomIndex, 1) = RndNums(Cnt, 1)
  Next
  Application.ReplaceFormat.Clear
End Sub

 

[Johncobb]

Thank you experts for your help. Both codes work.
Now I would like to add one more thing.
At the end of the whole range of my numbers in column AP, at the end of each row of numbers, I would like that there would be displayed a count of numbers matched / highlighted on that row.
So I will not have to count matched and highlighted numbers in each row, but by looking at the column AP it will show me how many matched in each row.
And if the count number in column AP is 3 or 2 or 1 or 0 that number should be in red font.
I hope that you know what I mean.

 

[Joe4]

Place this formula in cell AP1 and copy down for all rows of data:

=SUM(COUNTIFS(A1:AN1,$AO$1:$AO$20))

Then, select the data in column AP from AP1 down to your last row of data (291?), and use this Conditional Formatting formula:

=AP1<=3

and choose the red font color for the Formatting.

 

[Johncobb]

Place this formula in cell AP1 and copy down for all rows of data:

=SUM(COUNTIFS(A1:AN1,$AO$1:$AO$20))

Then, select the data in column AP from AP1 down to your last row of data (291?), and use this Conditional Formatting formula:

=AP1<=3

and choose the red font color for the Formatting.

Thank you very much again. It works.
One more thing (I hope this is the last one).
To modify my last request:
I want the same function but this time if the count number in column AP is 0 or 1 or 2 or 3 or 4 or 5 or 15 or 16 or 17 or 18 or 19 or 20 then the font should be red.
Thank you in advance.

 

[Joe4]

I want the same function but this time if the count number in column AP is 0 or 1 or 2 or 3 or 4 or 5 or 15 or 16 or 17 or 18 or 19 or 20 then the font should be red.

The Conditional Formatting rule should look something like:

=OR(AND(AP1>=0,AP1<=5),AND(AP1>=15,AP1<=20))

 

[Joe4]

I am glad that the last answer seemed to work for you.

However, in marking the "Solution" to the question, please mark the post that answered your original question, not the follow-ups.
Could you go back and mark the solution that you used for the original question you asked?

Thanks