never matching numbers

[mdshotgun]

Hello all,
If i have 16 numbers in col A,
what i want to do is in cols C,D show the numbers 8 in each column,but the numbers can never match.what i mean if number 7 is in C2 it can not appear again in either column until i hit F9.
Then in cols E,F do the same for numbers 1 to 8.

I have attach a sample if you like to view it.Thanks

[ChemistB]

Here's one way. Insert two blank columns in front of column A. In the new column B, B1:B16 type =RAND()
In Cell A1, put the formula (dragged down)
=RANK(B1,$B$1:$B$16)

In E2 (dragged down)
=VLOOKUP(ROW(A1),$A$1:$C$16,3,FALSE)
In F2 (dragged down)
=VLOOKUP(ROW(A9),$A$1:$C$16,3,FALSE)
You can hide columns A and B if you like. The values in E and F will change with F9
Does this work for you?

[mdshotgun]

Yes it's perfect.

Thank you very much chemistB.