Random selection from range where the selection is constrained of whether or not another cell on the same row ISTEXT

[dailan]

Dear folks,

hope that all is well. I have a frustrating problem with a random selection from a range given that another cell on the same row is text/not blank.
Below table might help to explain the circumstances. What I would like to do is to select a row completely random, constrained on the fact that the dealer can't be blank/must be text. And then I would like to retrieve the values from the stochastically selected row. Hopefully it makes sense what I would like to do.


[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Dealer[/TD]
[TD]Pos[/TD]
[TD]Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]122[/TD]
[TD]PS[/TD]
[TD]1000[/TD]
[TD]Buy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]DS[/TD]
[TD]1500[/TD]
[TD]Buy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]Sell[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]TS[/TD]
[TD]1000[/TD]
[TD]Buy[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

Dailan,

If you'll allow me a worker column and a cell to get the RANDBETWEEN result then this should work.

	A	B	C	D	E	F	G	H	I	J	K	L

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Dealer[/TD]
[TD="align: center"]Pos[/TD]
[TD="align: center"]Type[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Count[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Dealer[/TD]
[TD="align: center"]Pos[/TD]
[TD="align: center"]Type[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]122[/TD]
[TD="align: center"]PS[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]TS[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]Buy[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]444[/TD]
[TD="align: center"]DS[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]321[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]Sell[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]TS[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]Buy[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=INDEX(A$2:A$5,$G$2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2
[/TH]
[TD="align: left"]=IF(B2="","",MAX($F$1:$F1)+1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=MATCH(RANDBETWEEN(1,MAX($F$2:$F$5)),$F$2:$F$5,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3
[/TH]
[TD="align: left"]=IF(B3="","",MAX($F$1:$F2)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dailan]

Dear Toadstool,

hope that all is well.

Many thanks for the reply. Seems to be working.So a thousand thanks to you!
Just if you have the interest/time; I'm a bit reluctant to adding calculated columns since it generally makes the file a bit too slow (it's a big file). Trying to create an array formula that would cope with the issue, but when trying it i get a "1" since the ranking of each line receives a "1" in value. Would you happen to know a way of also solving this?

{=RANDBETWEEN(1;MAX(IF(B2:B2000=""; ""; MAX($B$1:$B1)+1);IF(B2:B2000=""; ""; MAX($B$1:$B1)+1)))

}​

Thanking again in advance!

Kind regards

Dailan

 

[Toadstool]

Dailan,

Well the formula I proposed to copy down for each row isn't an array formula so will run quickly: =IF(B2="","",MAX($F$1:$F1)+1)

I can't see how to do this in one array formula (e.g. the RANDBETWEEN must run just once so it must return the target row number) but maybe another forum member can see it?

Sorry,
Toadstool
P.S. I see you have semicolon delimiters so suspect you're using OpenOffice Calc. I would recommend LibrOffice Calc as I believe it to be more robust and it allows you to change the Function delimiter by going to Tools, Options, LibreOffice Calc, Formula, Separators

 

[Fluff]

For reference, a lot of European countries use a ; rather than a , as the delimiter.