### Formula how to say "cannot be equal to"

Posted:

**Sat Nov 16, 2019 8:31 am**So I have a spreadsheet that generates a random area of interest and three traits for a fictional character. The traits are chosen from a list based on what their area of interest is. There are 12 possible areas of interest. The problem is that all three traits are chosen from the same list, so sometimes I get duplicates, which I don't want. Is there any way to say "choose a word from this list that is NOT equal to (previous selection)"?

Formula:

I have already tried doing this (simplified formula):

IF(((IF(E$12="Politics";INDEX(Traits.$D$2:$D$103;RANDBETWEEN(2;COUNTA(Traits.$D$2:$D$103)))))<>(E14));((IF(E$12="Politics";INDEX(Traits.$D$2:$D$103;RANDBETWEEN(2;COUNTA(Traits.$D$2:$D$103)))))))

(aka: if/then). It worked on the simplified formula, but the full formula seems to have overmaxed on characters, and when I hit enter a large portion of the equation was cut off. Is there a way to make this work?

Formula:

- Code: Select all Expand viewCollapse view
`IF(D$12="Politics";INDEX(Traits.$D$2:$D$103;RANDBETWEEN(2;COUNTA(Traits.$D$2:$D$103)));IF(D$12="Adventure/Strategy";INDEX(Traits.$A$2:$A$113;RANDBETWEEN(2;COUNTA(Traits.$A$2:$A$113)));IF(D$12="Cooking/Baking";INDEX(Traits.$B$2:$B$101;RANDBETWEEN(2;COUNTA(Traits.$B$2:$B$101)));IF(D$12="Art";INDEX(Traits.$K$2:$K$111;RANDBETWEEN(2;COUNTA(Traits.$K$2:$K$111)));IF(D$12="Volunteer";INDEX(Traits.$L$2:$L$146;RANDBETWEEN(2;COUNTA(Traits.$L$2:$L$146)));IF(D$12="Combat";INDEX(Traits.$J$2:$J$114;RANDBETWEEN(2;COUNTA(Traits.$J$2:$J$114)));IF(D$12="Medicine";INDEX(Traits.$H$2:$H$92;RANDBETWEEN(2;COUNTA(Traits.$H$2:$H$92)));IF(D$12="Business";INDEX(Traits.$G$2:$G$100;RANDBETWEEN(2;COUNTA(Traits.$G$2:$G$100)));IF(D$12="Science";INDEX(Traits.$F$2:$F$95;RANDBETWEEN(2;COUNTA(Traits.$F$2:$F$95)));IF(D$12="RecordUpkeep";INDEX(Traits.$E$2:$E$85;RANDBETWEEN(2;COUNTA(Traits.$E$2:$E$85)));IF(D$12="Other";INDEX(Traits.$I$2:$I$146;RANDBETWEEN(2;COUNTA(Traits.$I$2:$I$146)));IF(D$12="Crafts-work";INDEX(Traits.$C$2:$C$99;RANDBETWEEN(2;COUNTA(Traits.$C$2:$C$99)))))))))))))))`

I have already tried doing this (simplified formula):

IF(((IF(E$12="Politics";INDEX(Traits.$D$2:$D$103;RANDBETWEEN(2;COUNTA(Traits.$D$2:$D$103)))))<>(E14));((IF(E$12="Politics";INDEX(Traits.$D$2:$D$103;RANDBETWEEN(2;COUNTA(Traits.$D$2:$D$103)))))))

(aka: if/then). It worked on the simplified formula, but the full formula seems to have overmaxed on characters, and when I hit enter a large portion of the equation was cut off. Is there a way to make this work?