Rand Between Multiple Lists With No Duplicates

[andrew_milonavic]

Hi,

I have several lists in columns A through F and at the bottom of each column I'm using this formula: =IFERROR(INDEX(A$1:A$100,RANDBETWEEN(1,COUNTA(A$1:A$100)),1),"") to randomly select from the column list. If the lists has duplicates, which it does by design, how can I keep it from returning duplicates (matching another column)?

Example: If "Cat" is in column A and column B, how do I keep the results from being "Cat" in both columns.

Would prefer a formula solution instead of Macro.

Thanks

Andrew

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	List1	List2	List3	List4	List5	List6
2	Cat	Chihuahua	Dog	Alpaca	Zebra	Lynx
3	Dog	Salmon	Mouse	Bear	Yak	Salmon
4	Iguana	Parrot	Parrot	Cat	Cat	Mouse
5	Parrot	Owl	Giraffe	Dingo	Wolf	Narwhale
6	Shark	Mouse	Dragon	Elephant	Tiger	Owl
7	Elephant		Bear	Fox	Salmon	Parrot
8	Trout			Gorilla
9				Hare
10
11
12
13
99
100
101	Trout	Parrot	Mouse	Elephant	Zebra	Salmon
Sheet10

Cell Formulas
Range	Formula
A101	A101	=IFERROR(INDEX(A$1:A$100,RANDBETWEEN(1,COUNTA(A$1:A$100)),1),"")
B101:F101	B101	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$100)/(COUNTIF($A101:A101,B$2:B$100)=0)/(B$2:B$100<>""),RANDBETWEEN(1,SUMPRODUCT(--(B$2:B$100<>""),--(COUNTIF($A101:A101,B$2:B$100)=0))))),"")

Put your original formula in A101, then put the B101 formula in and drag to the right.

 

[andrew_milonavic]

Thanks @Eric W ,

That works perfectly! Greatly appreciated.

As a follow up, is there a way to specify columns? Say I wanted no duplicates in just columns A,C,E?

Thanks

Andrew

 

[Eric W]

Possible, but it depends on what you want. As described, you could just put your old formula in A,B,D, and F, and the new one in C and E.

 

[andrew_milonavic]

Thanks Eric,

I think I can make it work with what you've suggested and I've played around with your provided formula so I'm pretty sure I can adapt it when I expand the lists and various combinations.

Thanks again, greatly appreciated.

Andrew

 

[Eric W]

Happy to help!