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

 

[Joe4]

What is the exact range that you would like these 20 random numbers to appear in?
Do you want to create all 20 at once, or one at a time?

Where are these rows of 40 numbers?
What row do they start on?
Do they start in column A and go out to column AN?

 

[Joe4]

OK, I came up with something that should work.
You may have to modify slightly to fit your structure.

Let's say that you are wanting to populate the range A1:T1 (20 cells) with unrepeating numbers from 1-80, one at a time.
And let's say that you have a bunch of rows of data of 40 numbers, starting at cell A10 and going down.

The first thing we want to do is to set up Conditional Formatting on our current rows of data, to highlight the numbers as they get selected.
So, starting with cell A10, select ALL the rows and columns of existing numbers that you want to check.
Then, go to Conditional Formatting, select New Rule, then "Use a formula to determine which cells to format", and enter this formula:

=COUNTIF($A$1:$T$1,A10)>0

then click the Format button, go to the Fill tab, select the color you want to highlight the cell in, and click OK.

Then, add this VBA code to the workbook:

Sub PopulateRandomNum()
    
    Dim rng As Range
    Dim lcol As Long
    Dim rn As Long
    Dim cell As Range
    Dim ct As Long
    
'   Designate range to populate values in
    Set rng = Range("A1:T1")
    
'   Check to see if all columns filled in
    If Range("T1") <> "" Then
        MsgBox "All cells have been populated with random numbers", vbOKOnly, "STOP!"
        Exit Sub
    End If
    
'   Find next cell to populate
    If Range("A1") = "" Then
        Set cell = Range("A1")
    Else
        Set cell = Range("U1").End(xlToLeft).Offset(0, 1)
    End If

'   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

End Sub


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

If you like, you can add a button to your worksheet, and attach the "PopulateRandomNum" VBA procedure to the button, or assign a keyboard shortcut to this code to quickly and easily run it (by clicking the button or using the keyboard shortcut).

Each time you run it, it will populate the range A1:T1, one cell at a time.
And the Conditional Formatting will be invoked automatically and highlight the numbers, as they appear in your list.

 

[Johncobb]

What is the exact range that you would like these 20 random numbers to appear in?
Do you want to create all 20 at once, or one at a time?

Where are these rows of 40 numbers?
What row do they start on?
Do they start in column A and go out to column AN?

Hi Joe4,
Thank you for your reply.
I don't know much about VBA code and where to put it. I will answer your above questions.

The total range is A1:AN291
Automatically selected (between 1 and 80) 20 random numbers at once. This 20 numbers will be checked if they came up in each row and each row has 40 numbers.
First row starts at A1
Columns run from A to AN

I have something similar created before but I can not modify it for this spreadsheet.
In the previous one I entered a formula in cells AO1 to AO20 and that's where the random numbers were created, then they were searched in each row and highlighted with a colored cell.
In next column AP there was another formula that has showed how many numbers did come up in each row.
From memory I think that all this was done with formulas. Unfortunately I am not good at it therefore I have to bug you mr. expert
Thank you in advance for your help and if you need more explanations please let me know.
John

 

[Fluff]

What version of Excel are you using?

I suggest that you 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’)

 

[Joe4]

See here for how to insert VBA code: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

 

[Johncobb]

See here for how to insert VBA code: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

I have applied as per your instructions.
Only one number is generated and highlighted. There must be an error somewhere.
Below I provide copy what I have applied. Please check for errors. Thank you in advance.


Excel Formula:

=COUNTIF($AO$1:$AO$20,A1)>0

VBA Code:

Sub PopulateRandomNum()

Dim rng As Range
Dim lcol As Long
Dim rn As Long
Dim cell As Range
Dim ct As Long

' Designate range to populate values in
Set rng = Range("AO1:AO20")

' Check to see if all columns filled in
If Range("AO20") <> "" Then
MsgBox "All cells have been populated with random numbers", vbOKOnly, "STOP!"
Exit Sub
End If

' Find next cell to populate
If Range("AO1") = "" Then
Set cell = Range("AO1")
Else
Set cell = Range("AO20").End(xlToLeft).Offset(0, 1)
End If

' 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

End Sub


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

 

[Fluff]

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

 

[Joe4]

Only one number is generated and highlighted. There must be an error somewhere.

No, none at all.
From your original wording, it sounded like you might want to do one at a time. So that is the way I created it, and I mentioned that:

Each time you run it, it will populate the range A1:T1, one cell at a time.

If you want to do all 20 at once, you can use this variation of the main procedure:

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("A1:T1")
   
'   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

(Note that you still need the Function from my original code too).

 

[Rick Rothstein]

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

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?