9 Replies Latest reply: May 8, 2013 8:06 PM by Yellowbox
witmac Level 1 Level 1 (0 points)

I have been trying to create a function in numbers and getting stuck.

 

I have a spreadsheet that contains a column of scenes, and a character in each row. The corresponding cells are filled with true and false (whether a characteristic in the scene or not).

 

I want to compare the data to find:

1) compatible scenes that can rehearse simultaneously without missing any characters. The resulting a spreadsheet would look like an old time stable, where the scenes would be listed in both column header and row header.

 

I need a formula that compares two whole rows:

=IF(any Cell in rowX is not equal to its counterpart in rowY, "X","Compatible"

 

*It would be even better if X and Y were relative - but Can you make a reference that says $B(ROW()):$W(ROW())=$B(COLUMN()):$W(COLUMN()).   ?


iPhone 5, iOS 6, at and t
  • 1. Re: How do I compare two rows of data?
    Barry Level 7 Level 7 (29,180 points)

    Hi witmac.

    Don't you just love auto correction?

     

    After about three read-throughs, I think I've straightened out "a characteristic in the scene," and "an old time stable,"

    When you say "simultaneously," do you mean "simultaneously" or "during the same rehearsal call"? I have some difficuty envisioning simultaneous rehearsals of, say, three scenes involving the same characters.

     

    Assuming 'compatible' scenes are those that contain the same character set, then, yes, it's possible to set up the data in a manner from which it will be possible, using Numbers '09 on OS X, to extract the scene identifiers of all scenes 'compatible' with that one. Whether it's possible to do it using the layout you're using, I'm unable to say without a better understanding of what that layout is. A screen shot might help.

     

    Whether it can be done on Numbers for iOS is something better asked in the iWork for iOS community. The link will take you there.

     

    Regards,

    Barry

  • 2. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi witmac,

     

    Not an elegant solution, and please check my logic!

     

    Table 1: Check marks on concurrent scenes

    Screen Shot 2013-05-08 at 3.52.59 PM.png

    Table 2: your casting list

     

    Screen Shot 2013-05-08 at 3.55.28 PM.png

     

    Table 3 (to follow in a new post) is TRUE for characters that are needed in your chosen scenes.

     

    Two or more TRUE in a column means there is a clash in those concurrent scenes (shown in Cell B11 Table 1.)

     

    Screen shot of Table 3 to follow.

     

    Regards,

    Ian

  • 3. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    Screen Shot 2013-05-08 at 3.56.24 PM.png

     

    Conditional format (red if TRUE for ease of sorting out clashes)

     

    Please reply if this does not make sense.

     

    Regards,

    Ian.

  • 4. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    Formula in Cell B2 Table 3:

     

    =AND(Table 2 :: B2,Table 1 :: $B2)

     

    Fill down and right

     

    Ian.

  • 5. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    There is an error in my example. I played with various combinations of concurrent scenes, and some combinations show YES for compatible (in Cell B11 on Table 1) when it is impossible for a character to be in two places at once.

     

    The formula in B11 on Table 1 is:

     

    =IF(Table 3 :: B11:E11>1,"NO","YES")

     

    This does not always work. I do not know why. I formatted Row 11 as Number and that did not help. I changed the formula to:

     

    =IF(Table 3 :: 11:11>1,"NO","YES") and that did not help.

     

    Regards,

    Ian.

     

    Message was edited by: Yellowbox Row 11, not Row B

  • 6. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    Ho Hum,

     

    Trying another way. Here is Table 3 where Character 3 is required in two places at once. Therefore Table 3, Cell D11 shows 2.

     

    Screen Shot 2013-05-08 at 11.05.06 PM.png

     

    Revised formula in Cell B11, Table 1 is

     

    =IF(MAX(Table 3 :: B11:E11)>1,"NO","YES")

     

    I had a play with scene combinations in Table 1 and it seems to work.

     

    Ian.

  • 7. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    P.S. Formula in Cell B9, Table 3 is:

     

    =COUNTIF(B2:B9,"TRUE")

     

    and fill to the right.

     

    Ian.

  • 8. Re: How do I compare two rows of data?
    witmac Level 1 Level 1 (0 points)

    HI IAN,

     

    I'm not totally clear on what you did here-

     

    How does it pull the two scenes to compare?

     

    I think I need some kind of comparative Hlookup- If any pair of cells is Both True, return "Conflict"- If the pairs are BOth False, or TRUE?FALSE, Return "Compatible"

     

    Does that make any more sense?

  • 9. Re: How do I compare two rows of data?
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi witmac,

     

    Table 1 is where you put check marks against those scenes you want to run concurrently.

     

    Table 2 is the casting list (set by the playwright's script).

     

    Table 3 combines the two. A cell becomes TRUE if a character is required in any of the chosen scenes.

    Bottom row of Table 3 adds up the TRUE values for each character.

    If there is more than one TRUE in any column, that is a Conflict (an actor can't be in two scenes at once).

    If there is no value greater than 1, there is no conflict (Compatible).

     

    Bottom cell in Table 1 tests the maximum of Table 3 Row 11 and displays Conflict or Compatible.

     

    Once your casting list is entered, you only need to use Table 1.

     

    In my previous post I said Formula in Cell B9, Table 3 is:

    =COUNTIF(B2:B9,"TRUE")

    and fill to the right.

    I should have written 'Formula in Cell B11, Table 3'

     

    I you like, click on my blue name to see my profile and email address. Drop me a line if you would like me to send you the document. Easier to understand if you can play around with it.

     

    Regards,

    Ian.