3 Random Values from a list skipping blanks

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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!

    HI NBVC,

    Apologies for the delay in getting back. Just wanted to say thanks for the response - it worked perfectly! Only tweak I needed to make was "" to " ".


    Thanks again!:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login