Sub routine using rnd and look for values in array

[dailan]

Dear folks,

hope that all is well.

I am having some troubles with a vba code.
1. Generating random variables within an interval
2. Creating a loop where if the random number generated is within an earlier interval it would generate a new random number

So as for now I only get "1" on rows 10-19. And then I would like to create a loop that checks if the value generated is already in an array above the row of which the generated number is. If it is in this array then the loop would generate another random number.

Please see below code for what I have been attempting;

Sub RNG_Click()
r
Dim ws1, ws2 As Worksheet
Dim i, Nr_rows, stokastiskt1, RNG As Integer

Sheets("Stickprov").Activate

Set ws1 = Worksheets("Stickprov")
Set ws2 = Worksheets("Beviljade ansökningar")
Nr_rows = ws2.Range("A2").End(xlDown).Row
RNG = Int((Nr_rows * rnd) + 1)
stokastiskt1 = ws1.Range("A3:A9").value


For i = 10 To 19
    
    Cells(i, 1).value = RNG
    
    
       ' If Cells(i, 1) = stokastiskt1 Then
          '  Cells(i, 1).value = RNG
      '  End If
                        
                  
Next i

MsgBox "Sample is provided"


End Sub

As always, many thanks in advance!

Kind regards

Dailan

 

[DanteAmor]

You could explain with an example what data you have and what would be the result you want.

stokastiskt1 = ws1.Range("A3:A9").value

In that range you have 7 data.
And in your cycle you have 10 (For i = 10 To 19)
I think they should have the same number.

 

[dailan]

Dear DanteAmor,

thanks for your reply.

So in worksheet 2 I have a matrix of raw data.
"
Nr_rows = ws2.Range("A2").End(xlDown).Row
"

This gives me the total number of rows in that array, so no problems there.


And then I try to do the stochastic grab of the rows by using the
"
RNG = Int((Nr_rows * rnd) + 1)
"

As the next step I try to populate the first column in worksheet 1 with randomly picked rows from the total number of rows. A condition is that the picked number should not be the same as a randomly picked number in the array above, and if so, it should continue the loop until it randomly grabs another number.

Hopefully this answers your question; stokastiskt1 is the array above cells(i, 1) that shouldn't contain the randomly picked number. And the cells I would like to populate is rows 10-19. As it is now I get the same value in rows 10-19, and it doesn't take into account whatever value is in the stokastiskt1 array.

For i = 10 To 19

Cells(i, 1).value = RNG


' If Cells(i, 1) = stokastiskt1 Then
' Cells(i, 1).value = RNG
' End If


Next i


Hopefully all above helps to clarify. Please tell me if there something else I have to explain in order for to give a helpful comment.

Thanking in advance!

KR

Daniel

 

[DanteAmor]

You could explain with an example what data you have and what would be the result you want.

It would be more practical for me to understand with a simple example.

You have 2 sheets.
On sheet2 you have data, do you want to take 10 random data from sheet2 and put it on sheet1?

If not, because I am not understanding.

You could forget your code for a moment and explain with an example what data you have and what you want in the output.

Something like this:

I have this on the sheet "Beviljade ansökningar"

varios 16jul2020 inche caracter.xlsm
	A
1
2	Data2
3	Data3
4	Data4
5	Data5
6	Data6
7	Data7
8	Data8
9	Data9
10	Data10
11	Data11
12	Data12
13	Data13
14	Data14
15	Data15
16	Data16
17	Data17
18	Data18
19	Data19
20	Data20
21
Beviljade ansökningar

And I want the next Output.

varios 16jul2020 inche caracter.xlsm
	A
10
11
12
13
14
15
16
17
18
19
Stickprov

So you put your examples of what you have and the output you want, and explain a couple of those data in the output.

To put examples use the XL2BB tool, look in my signature or here:

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[dailan]

Dear DanteAmor,

hope that the weekend was rewarding and that all is well in other regards.

Please see below;

On sheet 2 I have the raw data that I would like to extract to sheet 1. Looks like below. And then I have some additional columns to the right, which are not necessary to display in order to answer the question.

Kundnummer_full	DB
3004703557537	1205778
2001083994331	1206165
2005552484039	1205691
2005362892330	1206997
3006539659734	1206497
2004861900439	1206368
2004566483038	1206393
3002766550431	1205971
2008494608236	1205969
3002905890334	1206179
2001972835835	1206305
2002293785436	1206325
3003490196236	1205187
3004202425632	1206009
2003226638734	1206021
2000285371231	1205821
2006619760338	1206144
2008193746030	1206308
2002665591131	1207024
3009787828134	1205851
3007080527130	1206388
3001146043539	1206250
3007426503431	1206364

And what I am trying to achieve is the thing on sheet 1 (stickprov), as below. Rows 3-9 are the rows where I am trying just without creating a subroutine. Rows 10-20 is when using the subroutine. So what I would like to create is a loop where;
1. taking a random row (number) from the first column in sheet 2
2. checking if the randomly picked number already is already picked in the rows above it. If so the loop should continue until the randomly picked number is unique. To take as an example - the number picked in row 11 would breach this criteria thus making it necessary to continue selecting a random number until the number picked is unique.

Kontroll kredithandläggning - Stickprov (kreditpolicy 12.3) Corniche.xlsm
	A	B	C	D
2	Rank	Datum för beviljande	Produkt	Ansöknings-id
3	526	2020-05-15	Flexkontot	1209205
4	573	2020-05-19	Merax	1212084
5	304	2020-05-12	Kontantfinans	1207585
6	1145	2020-05-29	Brixo	1217741
7	1070	2020-05-29	Brixo	1217554
8	850	2020-05-26	Flexkontot	1215203
9	832	2020-05-26	Kontantfinans	1206469
10	894	2020-05-27	Brixo	1215736
11	894	2020-05-27	Brixo	1215736
12	894	2020-05-27	Brixo	1215736
13	894	2020-05-27	Brixo	1215736
14	894	2020-05-27	Brixo	1215736
15	894	2020-05-27	Brixo	1215736
16	894	2020-05-27	Brixo	1215736
17	894	2020-05-27	Brixo	1215736
18	894	2020-05-27	Brixo	1215736
19	894	2020-05-27	Brixo	1215736
20	1025	2020-05-28	Brixo	1216844
21	1109	2020-05-29	Brixo	1217721
22	476	2020-05-15	Brixo	1211488
Stickprov

Cell Formulas
Range	Formula
B3:B22	B3	=INDEX('Beviljade ansökningar'!$A$2:$R$8528, A3, 12)
C3:C22	C3	=IF(LEFT(INDEX('Beviljade ansökningar'!$A$2:$R$8979, A3, 1), 3)="100", "Kontantfinans", IF(LEFT(INDEX('Beviljade ansökningar'!$A$2:$R$8979, A3, 1), 3)="200", "Brixo", IF(LEFT(INDEX('Beviljade ansökningar'!$A$2:$R$8979, A3, 1), 3)="300", "Flexkontot", IF(LEFT(INDEX('Beviljade ansökningar'!$A$2:$R$8979, A3, 1), 3)="400", "Merax",))))
D3:D22	D3	=INDEX('Beviljade ansökningar'!$A$2:$R$8528, A3, 2)
A3	A3	=RAND()*COUNT('Beviljade ansökningar'!A:A)
A4:A9,A20:A22	A4	=IF(RAND()*COUNT('Beviljade ansökningar'!A:A)=$A$3:A3, RAND()*COUNT('Beviljade ansökningar'!A:A), RAND()*COUNT('Beviljade ansökningar'!A:A))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hopefully above clarifies a bit more exactly what I am trying to do.

 

[DanteAmor]

Sorry, but I still don't understand, especially since in your two sheets I don't see an example of a relationship between the two sheets.
Let's do an example with less data.

Assuming, in the "Beviljade ansökningar" sheet you have 10 data, from cell A2 to cell A11, only 10 rows with data, for example:

Dante Amor
	A	B	C
1	Kundnummer_full	DB
2	3004703557537	1205778
3	2001083994331	1206165
4	2005552484039	1205691
5	2005362892330	1206997
6	3006539659734	1206497
7	2004861900439	1206368
8	2004566483038	1206393
9	3002766550431	1205971
10	2008494608236	1205969
11	3002905890334	1206179
12
Beviljade ansökningar

So you have 10 numbers: 1,2,3,4,5,6,7,8,9,10.
You want those 10 numbers to be presented randomly on the "Stickprov" sheet, for example:

Dante Amor
	A	B
1
2	Rank
3	1
4	3
5	5
6	6
7	9
8	4
9	8
10	7
11	10
12	2
13
Stickprov

If the above is correct, try this:

Sub test_arr_1()
  Dim i As Long, x As Long, y As Long, lr As Long
  Dim arr As Variant
  Dim ws1 As Worksheet, ws2 As Worksheet
  
  Set ws1 = Sheets("Stickprov")
  Set ws2 = Worksheets("Beviljade ansökningar")
  lr = ws2.Range("A" & Rows.Count).End(3).Row
  
  arr = Evaluate("=row(1:" & lr - 1 & ")")
  Randomize
  For i = 1 To UBound(arr)
    x = Int(UBound(arr) * Rnd + 1)
    y = arr(x, 1)
    arr(x, 1) = arr(i, 1)
    arr(i, 1) = y
  Next
  ws1.Range("A3").Resize(UBound(arr)).Value = arr
End Sub

 

[dailan]

Dear DanteAmor,

hope all is well also today.

Your patience is absolutely amazing - many would have given up trying to help me. The solution presented by you works, given that I receive the rows in a random order and I don't risk of getting duplicates.

Many thanks!

Kind regards

Daniel

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.