Find and place number between two numbers

[MaxL76]

Hi All,
How all you going?
My question is:
I have row of random numbers from 1 to 70 on A1 - Q1.
I would like find and place on S1 number between 29 - 42 that exist on row A1 - Q1.
On T1 i would like find and place number that between 29 - 42 but not equal to S1.

Thanks a lot

 

[JEC]

With VBA. Before you use this, make sure you delete the question marks first.

Sub jec()
 Dim ar, i As Long
 ar = Range("A1:Q1")

 Randomize
 With CreateObject("System.Collections.SortedList")
   For i = 1 To UBound(ar, 2)
     If InStr("|" & Join(Array([S1], [T1]), "|") & "|", "|" & ar(1, i) & "|") = 0 Then .Item(Rnd) = ar(1, i)
   Next
   If [S1] = "" Then [S1] = .getbyindex(0): Exit Sub
   If [U1] = "" Then [V1].End(xlToLeft).Offset(, 1) = .getbyindex(0)
 End With
End Sub

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	48	24	59	2	66	38	44	29	13	5	58	16	54	9	28	65	1		28	29	38
2																			28	42
Sheet1

Cell Formulas
Range	Formula
A1:Q1	A1	=RANDBETWEEN(1,70)
S1:U1	S1	=AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=$S$2)/($A$1:$Q$1<=$T$2)/(COUNTIF($R$1:R1,$A$1:$Q$1)=0),1)

 

[JEC]

I didn't include your boundaries in my previous code

Sub jec()
 Dim ar, a, i As Long
 ar = Range("A1:Q1")
  
 Randomize
 With CreateObject("System.Collections.SortedList")
   For i = 1 To UBound(ar, 2)
     If InStr("|" & Join(Array([S1], [T1], [U1]), "|") & "|", "|" & ar(1, i) & "|") = 0 Then .Item(Rnd) = ar(1, i)
   Next
   For i = 0 To .Count - 1
     a = .getbyindex(i)
     If a > 29 And a < 42 Then
        If [S1] = "" Then [S1] = a: Exit Sub
        If [U1] = "" Then [V1].End(xlToLeft).Offset(, 1) = a: Exit Sub
     End If
   Next
 End With
End Sub

 

[JEC]

You actually don't need the values in A1:Q1. Try this

Sub jecc()
 Dim jv, a, i As Long, x As Long
 With Application
    jv = .RandArray(70, 1)
    For i = 1 To UBound(jv)
       a = .Match(.Small(jv, i), jv, 0)
       If a > 29 And a < 42 Then Cells(1, 19).Offset(, x) = a: x = x + 1
       If x = 3 Then Exit Sub
    Next
 End With
End Sub

 

[Legacy 143009]

S1:

=INDEX($A$1:$Q$1,LARGE(IF($A$1:$Q$1>29,IF($A$1:$Q$1<42,COLUMN($A$1:$Q$1),0),0),RANDBETWEEN(1,COUNTIFS($A$1:$Q$1,">29",$A$1:$Q$1,"<42"))))

This is an array formula. Hitting only Enter is not enough. Press Ctrl+Shift+Enter together after paste.

Note: I think this formula does not guarantee unique values. You should try.

 

[MaxL76]

With VBA. Before you use this, make sure you delete the question marks first.

Sub jec()
 Dim ar, i As Long
 ar = Range("A1:Q1")

 Randomize
 With CreateObject("System.Collections.SortedList")
   For i = 1 To UBound(ar, 2)
     If InStr("|" & Join(Array([S1], [T1]), "|") & "|", "|" & ar(1, i) & "|") = 0 Then .Item(Rnd) = ar(1, i)
   Next
   If [S1] = "" Then [S1] = .getbyindex(0): Exit Sub
   If [U1] = "" Then [V1].End(xlToLeft).Offset(, 1) = .getbyindex(0)
 End With
End Sub

Thanks JEC, i am still learning excel, VBA for me will be more difficult, but i will try it, just need understand what each row doing.

 

[MaxL76]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	48	24	59	2	66	38	44	29	13	5	58	16	54	9	28	65	1		28	29	38
2																			28	42
Sheet1

Cell Formulas
Range	Formula
A1:Q1	A1	=RANDBETWEEN(1,70)
S1:U1	S1	=AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=$S$2)/($A$1:$Q$1<=$T$2)/(COUNTIF($R$1:R1,$A$1:$Q$1)=0),1)

Hi Phuoc,
Wow, thanks, but i thinking i am little confuse you sorry about that.
i don't need run random formula, because i know all 17 numbers from A1 : Q1.
So i need only find and place them from S1:Y1.
Thanks again,
Max

 

[MaxL76]

S1:

=INDEX($A$1:$Q$1,LARGE(IF($A$1:$Q$1>29,IF($A$1:$Q$1<42,COLUMN($A$1:$Q$1),0),0),RANDBETWEEN(1,COUNTIFS($A$1:$Q$1,">29",$A$1:$Q$1,"<42"))))

This is an array formula. Hitting only Enter is not enough. Press Ctrl+Shift+Enter together after paste.

Note: I think this formula does not guarantee unique values. You should try.

Hi Flashbond,
Thanks for the answer, i tried it, the trying was unsuccessful.
also this formula have random formula, i don't need it because i know which numbers will be between A1:Q1.

Best Regards.
Max

 

[MaxL76]

I did this:
=IF(AND($A$2>=29,$A$2<=42),$A$2,IF(AND($B$2>=29,$B$2<=42),$B$2,IF(AND($C$2>=29,$C$2<=42),$C$2,IF(AND($D$2>=29,$D$2<=42),$D$2,IF(AND($E$2>=29,$E$2<=42),$E$2,IF(AND($F$2>=29,$F$2<=42),$F$2,IF(AND($G$2>=29,$G$2<=42),$G$2,IF(AND($H$2>=29,$H$2<=42),$H$2,IF(AND($I$2>=29,$I$2<=42),$I$2,IF(AND($J$2>=29,$J$2<=42),$J$2,IF(AND($K$2>=29,$K$2<=42),$K$2,IF(AND($L$2>=29,$L$2<=42),$L$2,IF(AND($M$2>=29,$M$2<=42),$M$2,IF(AND($N$2>=29,$N$2<=42),$N$2,IF(AND($O$2>=29,$O$2<=42),$O$2,IF(AND($P$2>=29,$P$2<=42),$P$2,IF(AND($Q$2>=29,$Q$2<=42),$Q$2,"")))))))))))))))))

and get this:

now i need explore how to fill on T1 number that not equal to S1

Thanks,