Search code examples
excelfunctionrandomduplicates

Having issues not including duplicate words randomly populating 3 columns in Excel

I'm populating Col A, Col B, and Col C with random text drawn from a current list of words in Col E. The words are supposed to populate 25 rows down in A, B, and C.

=INDEX(SORTBY(E2:E26, RANDARRAY(ROWS(E2:E26))), SEQUENCE(25))

The above function works as desired, however, the issue I'm having is every IF function I try to come up with to not have cells populate with duplicated words fail. No words are supposed to duplicate on any of the 25 rows. For example: if Col A has the word "Josh" in row 12, Col B and Col C are not to have the word Josh in their cells and are supposed to display an empty cell.

The latest attempt is below (I know it's ugly and definitely not correct but I'm pretty new to Excel):

=IF(INDEX(SORTBY(E2:E26, RANDARRAY(ROWS(E2:E26))), SEQUENCE(25))=A2:A101," ", INDEX(SORTBY(E2:E26, RANDARRAY(ROWS(E2:E26))), SEQUENCE(25)))

The above works somewhat but it only produces one word and not 25 as needed and I need it to compare every row in each col (A, B, and C) to ensure no duplicates exist on that particular row.

Here is an example of columns producing undesired duplicates.

Col A, B, and C outputs with duplicates showing

NOTE: The empty cells showing in the image are NOT produced from the function working, they are produced because the list this information is pulled from intentionally has blank cells.

I've searched on Stackoverflow as well as searched extensively online but have not been able to find a working solution.

Any help or suggestions to guide me to a working function that does what it's supposed to, I'd be very appreciative.

I've tried testing various forms of the IF function with no success.

Solution

  • For simplicity this formula generates random integer for testing.
    If there is at least one pair of data in a row, then the formula return an empty string for that row's cells.

    =LET(tab,INT(RANDARRAY(25,3,1,25)),
part,IF((CHOOSECOLS(tab,1)=CHOOSECOLS(tab,2))+ 
 (CHOOSECOLS(tab,1)=CHOOSECOLS(tab,3))+ 
 (CHOOSECOLS(tab,3)=CHOOSECOLS(tab,2)),"",tab),
part)

    enter image description here

    To apply this for text:

    1. Directly in the formula
    J1:J77 is the range of the words list.
    RANDARRAY last argument (77) is the max number of the items in the list.
    The tab variable will contains the random generated table of the words.
    RANDARRAY first argument (25) is the number of rows to generate.

    =LET(tab,INDEX(J1:J77,INT(RANDARRAY(25,3,1,77)),1), part,IF((CHOOSECOLS(tab,1)=CHOOSECOLS(tab,2))+(CHOOSECOLS(tab,1)=CHOOSECOLS(tab,3)) +(CHOOSECOLS(tab,3)=CHOOSECOLS(tab,2)),"",tab), part)

    enter image description here

    2. Create another table somewhere on the sheet using the table of numbers.

    The formula:

    =IFERROR(INDEX(J1:J77,A1:C25,1),"")