# Find and place number between two numbers



## MaxL76 (Dec 22, 2022)

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 (Dec 22, 2022)

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 (Dec 22, 2022)

Try this

Book1ABCDEFGHIJKLMNOPQRSTU148245926638442913558165492865128293822842Sheet1Cell FormulasRangeFormulaA1:Q1A1=RANDBETWEEN(1,70)S1:U1S1=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 (Dec 22, 2022)

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 (Dec 22, 2022)

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
```


----------



## Flashbond (Dec 22, 2022)

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 (Dec 23, 2022)

JEC said:


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


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 (Dec 23, 2022)

Phuoc said:


> Try this
> 
> Book1ABCDEFGHIJKLMNOPQRSTU148245926638442913558165492865128293822842Sheet1Cell FormulasRangeFormulaA1:Q1A1=RANDBETWEEN(1,70)S1:U1S1=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 (Dec 23, 2022)

Flashbond said:


> 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"))))
> ...


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 (Dec 23, 2022)

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,


----------



## MaxL76 (Dec 22, 2022)

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


----------



## Phuoc (Dec 23, 2022)

Try this in S1 and copy to right:

=IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=29)/($A$1:$Q$1<=42),COLUMNS($S$1:S1)),"")


----------



## MaxL76 (Dec 23, 2022)

Phuoc said:


> Try this in S1 and copy to right:
> 
> =IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=29)/($A$1:$Q$1<=42),COLUMNS($S$1:S1)),"")


Thanks a lot Phuoc,
it's do a work, wow.
Can you give me small explanation how it work.

Thansk,


----------



## MaxL76 (Dec 23, 2022)

Another challenge:
Same situation but now i have to ranges
1: 1 - 8;
2: 61 - 70;

Thanks a lot


----------



## Phuoc (Dec 26, 2022)

MaxL76 said:


> Another challenge:
> Same situation but now i have to ranges
> 1: 1 - 8;
> 2: 61 - 70;
> ...



1. 1-8

=IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=1)/($A$1:$Q$1<=8),COLUMNS($S$1:S1)),"")

2. 61-70

=IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=61)/($A$1:$Q$1<=70),COLUMNS($S$1:S1)),"")

...


----------



## MaxL76 (Dec 27, 2022)

Phuoc said:


> 1. 1-8
> 
> =IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=1)/($A$1:$Q$1<=8),COLUMNS($S$1:S1)),"")
> 
> ...


Hi Phuoc,
Thanks,
Sorry but i meant check two ranges in same time.

BR
Max


----------



## MaxL76 (Dec 28, 2022)

MaxL76 said:


> Hi Phuoc,
> Thanks,
> Sorry but i meant check two ranges in same time.
> 
> ...


What i meant it if i cad do that:
=IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=1)/($A$1:$Q$1<=8),COLUMNS($S$1:S1)),"") and IFERROR(AGGREGATE(15,6,$A$1:$Q$1/($A$1:$Q$1>=61)/($A$1:$Q$1<=70),COLUMNS($S$1:S1)),"")
Thanks,


----------

