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)) ...
Cis the data to be placed in
Fis the Random Generator result (a number between 1 and 122)
Nis a sequential fixed number based on the number of cells that have data in Column
C(typically between 1 & 122).
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
J6=2, this is acceptable, however if
J6=3, this is not acceptable because
J6 are duplicates.
I displays data from Column
=INDIRECT("B"&INDIRECT("F"&N1)), etc., but if I can get the spreadsheet to recalculate automatically based on duplicates in Column
I will follow Column
J because both
J are using the values in
F to copy data from Columns
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.
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.
Note: If the answer requires using VBA code, I will need lots of help! :o)
Please refer to Screen-Shot at http://imgur.com/RcQaexo
A is a (manual) Song counter to indicate how many songs are on the list
B contains Song Titles with the Key (in parenthesis) that the song is to be played in
C indicates the person who sings the song. Note: "Inst." in Column
C indicates that the song is an instrumental - no vocal.
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.
C are sorted by Song Title (Column
B) then Genre (Column
E is the random number generator:
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.
G is a vertical spacer (no data)
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.
I are Song Titles copied from Column
B based on the random number generator (Column
E) and "fixed" numbers in 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
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.
L is another spacer column - no data
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
K are printed before each gig - a copy for each person in the band.
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
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
J3 and again in
J9. Also, notice that "Phil" appears in
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.