3 Random Values from a list skipping blanks

  • Hi All,

    Firstly thanks in advance for the free advice - its much appreciated. So I have a main list of made up of names, Teams and Other text. The list is 100 rows but may not always be fully populated (e.g. sometimes it have 50 rows, the following month it may have 100), so some rows could be blank. The maximum population of this list will never exceed 100. In a separate list I want to retrieve 3 random names from the main list.


    I've tried this formula without success: =INDEX($D:$D,INDEX(MODE.MULT(IF($D$8:$D$100<>{"",""},ROW($D$8:$D$100))),RANDBETWEEN(1,SUM(N(LEN($D$8:$D$100)>0)))))


    Where D is the column in the main list with Names. Problem: This formula quite often produces duplicates, or blank cells.


    Not sure where I am going wrong with this one. I have attached screenshots of how the lists are made up if that helps.


    Happy to provide more info if needed. Thanks!

  • Create a couple of helper columns adjacent to the main list.

    These columns can be hidden.


    In F4: =IF(E4="","",RAND()) copied down the full 100 rows

    In G4: =IFERROR(RANK(F4,$F$4:$F$104),"") copied down the full 100 rows.


    Now, in I4: =INDEX($C$4:$C$104,MATCH(ROWS(I$4:I4),$G$4:$G$104,0)) copied down 3 rows.


    Adjust formulas to suit your actual data ranges.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016