Randomly choose one instance of a text from multiple occurrences in a list or column

    I am completely new to creating formulas in excel, and would be most grateful if you could help provide a formula that will help me do the following:


    I have a column of text in say Column A. My formula will be entered in say cell C1. I want to be able to put a text value in cell B1 and for the formula to search for a matching instance of that same value in column A, and if the instance of that text is found more than once, for the formula to randomly pick one of those instances and return the contents of that entire cell in cell C1.


    Here's a mock-up image of my example:


    [ATTACH=CONFIG]71991[/ATTACH]


    Therefore, in the example above, every time the sheet is re-calculated, C1 will return either the value Pink Shoe, Tickled Pink, Pink Spots or Pinky & Perky.


    Hope the above makes sense, and your input would be greatly appreciated!

    Re: Randomly choose one instance of a text from multiple occurrences in a list or col


    Try this Array* formula:


    [COLOR="#0000FF"]=INDEX(A1:A9,SMALL(IF(ISNUMBER(SEARCH(B1,A1:A9)),ROW(A1:A9)-ROW(A1)+1),RANDBETWEEN(1,COUNTIF(A1:A9,"*"&B1&"*"))))[/COLOR]


    [arf]*[/arf]

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

    Re: Randomly choose one instance of a text from multiple occurrences in a list or col


    Many thanks for the prompt reply. My apologies however because I forgot to mention I'm using excel 2003 which I see doesn't recognise the RANDBETWEEN function?
    Have you got a re-working of the formula that would work in excel 2003?


    Many thanks once again.

    Re: Randomly choose one instance of a text from multiple occurrences in a list or col


    Are you sure it isn't available in 2003?


    I think you might need to go to Tools|Addins and select Analysis Toolpak. This should activate the function.

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

    Re: Randomly choose one instance of a text from multiple occurrences in a list or col


    Sorry! You're absolutely right. I did what you said and the formula works a dream!
    Can't thank you enough, NVBC. Much appreciated.

    Re: Randomly choose one instance of a text from multiple occurrences in a list or col


    Great. You're welcome.

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

Participate now!

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

Register Yourself Login