Non-duplicate randomized word list (Index, RandBetween, & CountA)

**newand** · 10-13-2017, 07:45 PM

Hi, I've been working on generating random word from a list and is stuck on "exclusion." By that I mean, is there a way for index to dynamically change (maybe offset) as it go down the column? Or to ignore a certain cell in the range?

For example:

I have a list of terms on F2:F11. On I2:I11, I have two randomized terms.

What I would like to do is:
I2: =INDEX($F$3:$F$11,RANDBETWEEN(1,COUNTA($F$3:$F$11)),1)
I3: =INDEX($F$2,F4:$F$11,RANDBETWEEN(1,COUNTA($F$2,F4:$F$11)),1)
I4: =INDEX($F$2:F3,,F4:$F$11,RANDBETWEEN(1,COUNTA($F$2,F4:$F$11)),1)

I know the above do not work so I'm looking for a solution that would work. Pretty much, Column I will ignore the same row on Column F (F2) when it randomizes, so to not have duplicates. For example:I do not want aggravate; amorous on I2.

Thank you for all your help.

**gfranco** · 10-13-2017, 09:59 PM

Good evening.
See if the attachment helps you.

**newand** · 10-13-2017, 10:14 PM

Hi, gfranco. Thank you for your help. However, your attachment still uses the cell I do not want to use. For example, I2 should only contain values from F3:F11. So I2 should not contain "aggravate" but can have any other value (e.g., antebellum; amicable).

For my case, repetition meaning they should not contain the same value in the same row, from F2 to I2. All should be different.

Thanks again for taking your time out to assist me.

**gfranco** · 10-13-2017, 10:24 PM

OK.
I'm out of ideas now.
If there is any, I'll post.

**newand** · 10-18-2017, 01:47 AM

Just want to give this one a last bump. Thanks.

**nilem** · 10-18-2017, 02:55 AM

Hi Newand,
maybe UDF

Please Login or Register  to view this content.

**newand** · 10-18-2017, 03:33 PM

Hi nilem,

Wow, that's amazing! I didn't know there's such thing as UDF. Thank you so much. I'll be studying this code you've given me.

Thank you, again!

**newand** · 10-20-2017, 09:56 PM

Hi, nilem,

I've been working with the UDF you created for me. I tried to extend your UDF to add more exclusions. The UDF seems to still work but the exclusions do not. It is possible to add more exclusions to the formula? And use the first randomized term as as an exlcusion? Please see the attachment for more info.

Thanks!

**nilem** · 10-21-2017, 08:45 AM

Hi Newand,
maybe something like this

Please Login or Register  to view this content.

**newand** · 10-22-2017, 09:54 AM

wow, the codes are much more complex than I thought. Thanks! All's good now.

Non-duplicate randomized word list (Index, RandBetween, & CountA)

LinkBack

Thread Tools

Rate This Thread

Display

Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Re: Non-duplicate randomized word list (Index, RandBetween, & CountA)

Thread Information

Users Browsing this Thread

Similar Threads

Randomized List Pulled from Filtered Table

[SOLVED] Randbetween - not to repeat or duplicate

[SOLVED] matching randomized list title with specific cell

[SOLVED] Trying to generate randomized list of tweets based on existing data set

Randomized call out list

List of names from duplicate data? / Take first word of cell only

How to prevent duplicate using randbetween?

Bookmarks

Bookmarks

Posting Permissions