random(ish) list...

[dobby1303]

Hi all,

I'm wanting to generate a list of 10 values from column A2:A..., but only from those where the value in column C2:C... is less than the value in cell E1.

Does anyone know if there is a formula that is able to do this?

 

[Rick Rothstein]

I cannot think of a way for a formula to do what you asked for; however, I do have a macro solution that you can use. I was not sure where the output was supposed to go to, so I outputted the list of 10 values starting at cell F1 (change what I highlighted in red to the actual starting output cell). Also note that you can change the 10 I assigned to the HowMany variable (highlighted in blue) to any number assuming you wanted a list of more or less than 10 outputted.

Sub Get10RandomValuesWithCondition()
  Dim HowMany As Long, Cnt As Long, RandomIndex As Long, Tmp As Variant, Arr As Variant
  [B][COLOR="#0000FF"]HowMany = 10[/COLOR][/B]
  Arr = Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(" & Range("C2", Cells(Rows.Count, "C").End(xlUp)).Address & "< E1," & Range("A2", Cells(Rows.Count, "A").End(xlUp)).Address & ","""")")))))
  Randomize
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  For Cnt = 1 To HowMany
    Range("[B][COLOR="#FF0000"]F1[/COLOR][/B]").Resize(HowMany) = Application.Transpose(Arr)
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MoveStatesUpOneRowInColumnF) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Eric W]

You can do it with formulas. To make it as general as possible, I assumed that the values in column A are text (not numeric), and that duplicates are possible. I also allowed the range to have empty rows on the end. Removing any of these constraints would make the formula simpler. Note that in the case of duplicates (see "Don" in the example below), you can get as many duplicates in the list are there are valid values in column C. Don is on the list twice (from C5 and C19), but he won't show up from C18.

Excel 2012

	A	B	C	D	E	F
1	Values				10	List
2	Amy		1			June
3	Beth		2			Cal
4	Cal		7			Oscar
5	Don		9			Beth
6	Erin		2			Iris
7	Faye		11			Don
8	Greg		13			Nadia
9	Hank		17			Don
10	Iris		5			Paul
11	June		4			Tonya
12	Kevin		22
13	Lou		18
14	Maria		4
15	Nadia		7
16	Oscar		8
17	Paul		9
18	Don		44
19	Don		4
20	Sandy		2
21	Tonya		1
22

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula F2 {=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$30<>"",IF($C$2:$C$30<$E$1,IF(COUNTIFS($A$2:$A$30,$A$2:$A$30,$C$2:$C$30,"<"&$E$1)>COUNTIF($F$1:$F1,$A$2:$A$30),ROW($A$2:$A$30)))),RANDBETWEEN(1,COUNTIFS($A$2:$A$30,"*?",$C$2:$C$30,"<"&$E$1)-ROW($F2)+ROW($F$2)))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[dobby1303]

I'm trying both to see which works best for me, a couple of add on bits of info and another question...

There are no duplicates in the original data
I don't know how long the original range is going to be

How would I edit either of these to run on another sheet? With the macro I get debug errors, I assume because it's looking at the page I'm trying to run it from and the formula freaks out and gives me the same value for the whole list...

Thanks again!

 

[Eric W]

For the formula, if there are not duplicates, the formula will still work just fine. If you start getting performance issues, let me know and I'll rewrite it so that it takes out that piece, and that might help. If you don't know how long the range will be, you can just pick a row somewhere below where you think the max would be. For example, if you think you'll have 100 values, change all the 30's in the formula to 200.

If you need to edit it to run on another sheet, just change the appropriate range references to include the sheet name. Note that the F references refer to the sheet the formula is on, and should not have a sheet name. If your original data is on Sheet3, you can put this formula in F2 of another sheet:

=IFERROR(INDEX(Sheet3!A:A,SMALL(IF(Sheet3!$A$2:$A$30<>"",IF(Sheet3!$C$2:$C$30<Sheet3!$E$1,IF(COUNTIFS(Sheet3!$A$2:$A$30,Sheet3!$A$2:$A$30,Sheet3!$C$2:$C$30,"<"&Sheet3!$E$1)>COUNTIF($F$1:$F1,Sheet3!$A$2:$A$30),ROW(Sheet3!$A$2:$A$30)))),RANDBETWEEN(1,COUNTIFS(Sheet3!$A$2:$A$30,"*?",Sheet3!$C$2:$C$30,"<"&Sheet3!$E$1)-ROW($F2)+ROW($F$1)+1))),"")