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=1and J6=2, this is acceptable, however if J1=1, J2=1, J3=3, J4=4, J5=3and 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.

  • I would avoid using VBA to recalculate because the run time is going to be based purely on chance. If you keep getting duplicate values the macro will simply keep running, possibly infinitely if you're unlucky enough. – Kyle Jan 6 '16 at 15:45

You can insert this formula into another column (in my case I used column K). It increments the value by one if it's the same as the previous value, unless the previous value is the max value, then it returns one. In K1, just use =J1, thenenter this formula in K2 and autofill down. Replace MAX_VALUE with the actual max value.

=IF(J2=K1,IF(J2=MAX_VALUE,1,J2+1),J2)

Here's an example of what it looks like, assuming a max value of 10:

J    K
2    2
6    6
6    7
7    8
7    7
10   10
10   1
10   10
  • Thank you for proposed solution, Kyle however it doesn't quite do the job. The problem is a little more complicated than just sorting numbers. Is there a way for me to post a screen-shot of my spreadsheet so I can better explain the overall problem? – B. Turner Jan 6 '16 at 19:27
  • @B.Turner You can edit your question with a link to your image on a site like imgur. – Kyle Jan 6 '16 at 20:01
  • I'm not sure how to do that, Kyle. We're going to have to pick this up later as I have a meeting and will be 'out of pocket' for the next few hours. Hopefully I can figure out what you are referring to when I get back. Thanks. – B. Turner Jan 6 '16 at 20:07
  • Since this is the first time I've tried to use SuperUser and am not totally familiar with site protocol, rules and procedures, I hope I didn't make everyone mad. If I did, I apologize. Thanks for allowing me to present my question. – B. Turner Jan 11 '16 at 6:09

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.