Get Combination Of Unique Values From N Columns Using Rand Or RandBetween Function

I appreciate all the help I have gotten in the past, and I realize that I ask some of the dumbest questions, but I need help again.

I have need of a formula using the random feature.

I have columns, Entrees, Vegetables 1, Vegetables 2, and Starches. They have 10 items in each column.

I need a formula that will choose one item from each column, and make sure that there are no duplicates (in the Vegetables column).

I've been working on this what seems forever, but cannot figure it out.

Can I get some help?

Thanks

Perhaps if you posted a sample spreadsheet of your data, it would be easier to supply you with a workable solution.

How do I post a sample spreadsheet?

Thanks

See attached, an unmanageable and grotesque formula, but it seems to work. There must be a better way. Would a user defined function be acceptable (a macro)?

Quote:

---

Originally Posted by papabill

How do I post a sample spreadsheet?

---

First click Reply or Reply with quote, then click Go Advanced and then click Manage Attachments and follow your nose.

Attachment 1635

Quote:

---

Originally Posted by alansidman

Perhaps if you posted a sample spreadsheet of your data, it would be easier to supply you with a workable solution.

---

Quote:

---

Originally Posted by papabill

and make sure that there are no duplicates (in the Vegetables column)

---

Why did you specifically refer to the vegetables column?!
See attached.

Quote:

---

Originally Posted by p45cal

Why did you specifically refer to the vegetables column?!

---

Sorry, Originally the Veg1 and Veg2 columns were identical (until my brain figured out more vegetables) and I didn't want the same veg twice

In line with p45cal, but a little more simpler approach maybe...

The only thing that came through was a page (SHEET 2) of numbers, with "=RAND()" as the ONLY formula in each cell. Sorry, but nothing about it makes sense.

Quote:

---

Originally Posted by papabill

The only thing that came through was a page (SHEET 2) of numbers, with "=RAND()" as the ONLY formula in each cell. Sorry, but nothing about it makes sense.

---

You should look more closely.

Yes, RANK is a better solution, so you could change the formulae in my offering:
C17:
=INDEX(A$3:A$12,RANK(M3,M$3:M$12))

D17:
=INDEX(C$3:C$12,RANK(N3,N$3:N$12))

E17:
=INDEX(E$3:E$12,RANK(O3,O$3:O$12))

F17:
=INDEX(G$3:G$12,RANK(P3,P$3:P$12))

then copy them down to fill 10 rows; much simpler than my:
=INDEX($A$3:$A$12,MATCH(LARGE(M$3:M$12,ROW()-ROW($C$16)),M$3:M$12,0))