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

[papabill]

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

[alansidman]

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

[papabill]

How do I post a sample spreadsheet?

Thanks

[p45cal]

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)?

[p45cal]

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.

[papabill]

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

[p45cal]

and make sure that there are no duplicates (in the Vegetables column)Why did you specifically refer to the vegetables column?!
See attached.

[papabill]

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

[Excel Fox]

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

[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.

[p45cal]

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))