### Scenario

Column `J`

displays data from column `C`

based on random selection from 2 other columns of numbers, i.e.:

```
J1: =INDIRECT("C"&INDIRECT("F"&N1))
J2: =INDIRECT("C"&INDIRECT("F"&N2))
J3: =INDIRECT("C"&INDIRECT("F"&N3))
...
```

WHERE:

- Column
`C`

is the data to be placed in`J1`

,`J2`

,`J3`

, etc. - Column
`F`

is the Random Generator result (a number between 1 and 122) - Column
`N`

is a sequential fixed number based on the number of cells that have data in Column`C`

(typically between 1 & 122).

### Problem

IF `J2`

= `J1`

, then basically I want to perform a "Calculate Full" (or similar function) until they are not equal and continue checking down the `J`

column to the end of the array. For example: If `J1=1`

, `J2=3`

, `J3=2`

, `J4=3`

, `J5=1`

and `J6=2`

, this is acceptable, however if `J1=1`

, `J2=1`

, `J3=3`

, `J4=4`

, `J5=3`

and `J6=3`

, this is not acceptable because `J1`

& `J2`

and `J5`

& `J6`

are duplicates.

Similarly, Column `I`

displays data from Column `B`

e.g.: `=INDIRECT("B"&INDIRECT("F"&N1))`

, etc., but if I can get the spreadsheet to recalculate automatically based on duplicates in Column `J`

, Column `I`

will follow Column `J`

because both `I`

& `J`

are using the values in `N`

& `F`

to copy data from Columns `B`

and `C`

respectively.

Everything I have tried thus far results in a circular reference error. I can manually "Calculate Full" but I still get duplicates (vertically in column `J`

) and that is not acceptable.

Column `B`

contains Song Titles and there are no duplicates. Column `C`

has performers names and there are numerous duplicates in Column `C`

because there are only 4 singers in the band and I would prefer that a singer doesn't have to sing 2 songs back-to-back.

Any suggestions?

Note: If the answer requires using VBA code, I will need lots of help! :o)

### More Info:

Please refer to Screen-Shot at http://imgur.com/RcQaexo

COLUMN DESCRIPTIONS:

Column `A`

is a (manual) Song counter to indicate how many songs are on the list

Column `B`

contains Song Titles with the Key (in parenthesis) that the song is to be played in

Column `C`

indicates the person who sings the song. Note: "Inst." in Column `C`

indicates that the song is an instrumental - no vocal.

Column `D`

indicates the genre of the song where "2" represents a 2-Step type of song. Other genre representations are "P" for Polka, "W" for Waltz, etc.
Columns `B`

& `C`

are sorted by Song Title (Column `B`

) then Genre (Column `D`

)

Column `E`

is the random number generator: `=Rand()`

Column `F`

generates a number based on Column `E`

and limits the range of numbers based on the quantity of songs in each genre: `=RANK(E1,E$1:E$53`

Note: there are 53 2-Step type songs.

Column `G`

is a vertical spacer (no data)

Column `H`

is another manual counter to keep track of the number of songs in each set. Typically there are 4 sets per night and each set can contain 8-13 songs depending on if the set is 45 minutes or 1 hour.

Column `I`

are Song Titles copied from Column `B`

based on the random number generator (Column `E`

) and "fixed" numbers in Column `N`

: `=INDIRECT("B"&INDIRECT("F"&N2))`

Column `J`

is the singer/performer copied from Column `C`

based on the random number generator (Column `E`

) and the same fixed numbers in Column `N`

: `=INDIRECT("C"&INDIRECT("F"&N2))`

Column `K`

indicates the genre of each song and is manually determined based on a certain "mix" of genres. The screen-shot example indicates the mix to be 2 2-step songs, followed by a waltz, followed by 2 more 2-steps, then a Slow song, etc. We as a band determine the genre mix based on past experience with certain venues.

Column `L`

is another spacer column - no data

Column `M`

is a color-coded copy of Column `K`

to aid in keeping track of the genre mix determined by the numbers in Column `N`

and Column `F`

.

Note: Columns `H`

, `I`

, `J`

& `K`

are printed before each gig - a copy for each person in the band.

Column `N`

is the Genre Mix Template and is color-coded the same as Column `M`

. Red = 2-Step, Blue = Waltz, Pink = Polka, Green = Slow, and Black = Rock 'n Roll. The numbers are manually typed in and are sequential beginning with the first song of a particular genre on the Master Song List (Columns `B`

, `C`

& `D`

) to the last number of that genre. The current genre layout is as follows: 2-Step songs range from 1-53, Polkas range from 54-68, Rock songs range from 69-90, Slow songs range from 91-111 and Waltz' range from 112-120.

There are 4 columns that don't appear on the screen-shot which keep track of the first and last genre numbers and are not particularly relevant to my original question.

As you can see on the screen-shot, "Jerry" appears in `J2`

& `J3`

and again in `J7`

, `J8`

& `J9`

. Also, notice that "Phil" appears in `J13`

& `J14`

. The goal here is to eliminate as many "row duplicates" as possible. Jerry sings as many as 60 songs of different genres, Chuck sings as many as 30 and Phil sings 20 songs, so there should be an adequate number of songs for each performer so that duplicates are not an issue.

I realize the information provided in my original post was rather inadequate, but hopefully this additional info will help us come up with a solution to my problem. Please let me know if I need to provide further explanation.