Select random row from a list after Autofilter has been applied

**gkizilis** · 04-30-2013, 09:39 AM

Hi all,

I would appreciate any help on this issue:

I am trying to select a random row from a list of rows in excel, after the rows have had an AutoFilter applied to them.

I have round the function to select a random row (with the help of INDEX) and RAND(), but I want the function to work on the filtered results, not all the results.

Any help of the matter would be greatly appreciated!

George

PS: I have uploaded the spreadsheet for convenience

**Tony Valko** · 04-30-2013, 10:51 AM

Try this array formula**:

=INDEX(C4:C74,SMALL(IF(SUBTOTAL(103,OFFSET(C4:C74,ROW(C4:C74)-MIN(ROW(C4:C74)),,1)),ROW(C4:C74)),RANDBETWEEN(1,SUBTOTAL(103,C4:C74)))-MIN(ROW(C4:C74))+1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.