(Old) Rand Between Multiple Lists With No Duplicates

[Sansao]

Hello Everyone!
Following the old post (Rand Between Multiple Lists With No Duplicates).

Is it possible to make it for two lines at the bottom?
We generate the names on the line 20. How can I create a new sort in line 21 without duplicating the names of the same column in line 20 and not duplicating the names of the line 21?

Example: If "Cat" is in B20, how can i keep it from beeing "Cat" again in B21 and also not duplicate "Cat" in line 21?

I'm using this:

Range	Formula
Cell Formulas
A20	A20	=IFERROR(INDEX(A$3:A$17,RANDBETWEEN(1,COUNTA(A$3:A$17)),1),"")
B20:D20	B20	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$17)/(COUNTIF($A20:A20;B$3:B$17)=0)/(B$3:B$17<>""),RANDBETWEEN(1,SUMPRODUCT(--(B$3:B$17<>""),--(COUNTIF($A20:A20;B$3:B$17)=0))))),"")

 

[Sansao]

Up

 

[Peter_SSs]

Could you insert a new blank column A then you could use the same formula dragged to all cells (Assuming that I have interpreted correctly)

22 03 24.xlsm
	A	B	C	D	E
1
2
3		cat	chihuahua	dog	dragon
4		dog	salmon	mouse	dingo
5		iguana	parrot	giraffe	wolf
6		parrot	cat	owl	salmon
7			mouse	bear	mouse
8					chihuahua
9
10
11
12
13
14
15
16
17
18
19
20		dog	cat	bear	dingo
21		parrot	chihuahua	giraffe	wolf
Rand

Cell Formulas
Range	Formula
B20:E21	B20	=LET(f,FILTER(B$3:B$17,(B$3:B$17<>"")*ISNA(MATCH(B$3:B$17,B19,0))*(ISNA(MATCH(B$3:B$17,$A20:A20,0)))),INDEX(f,RANDBETWEEN(1,ROWS(f))))

 

[Sansao]

Hi, Peter!
Thanks very much for your reply and help.

Well, your example looks fine, but actually, over time, it starts to duplicate values in the line and in the column, as you can see in the image below. That's what I don't want to happen. I applyed the formula you gave me in the range B20:E21

 

[Peter_SSs]

Are you sure that you applied the formula correctly and without alteration?
I have just run a macro to recalculate my test worksheet 10,000 times and check for duplicates each time. It showed no duplicates.

Copy/Paste my B20 formula into your B20 then drag the formula across and down.

If you are still getting that can you post your sheet with XL2BB as we cannot tell much at all from a picture.

 

[Peter_SSs]

As another double-check, start a new blank worksheet.

Copy my sample from post #3 by clicking this icon

at the top-left of my mini-sheet

Then select cell A1 in your new blank worksheet and Paste
Press F9 to recalculate repeatedly.

 

[Sansao]

Dear Peter,

You were right, the excel formula translator was making a mistake that I indentifyed and corrected. After that, everything worked perfectly.

Thank you so much for you time and help!

Wish you all the best! :D

 

[Peter_SSs]

You're welcome. Glad it is resolved.