## How do I compare two rows of data?

350 Views 9 Replies Latest reply: May 8, 2013 8:06 PM by Yellowbox
Calculating status...
Currently Being Moderated
May 7, 2013 7:18 PM

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
• Level 7 (28,790 points)
Currently Being Moderated
May 7, 2013 10:36 PM (in response to witmac)

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

• Level 4 (3,625 points)
Currently Being Moderated
May 7, 2013 11:00 PM (in response to witmac)

Hi witmac,

Not an elegant solution, and please check my logic!

Table 1: Check marks on concurrent scenes

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

• Level 4 (3,625 points)
Currently Being Moderated
May 7, 2013 11:02 PM (in response to Yellowbox)

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

Regards,

Ian.

• Level 4 (3,625 points)
Currently Being Moderated
May 7, 2013 11:05 PM (in response to Yellowbox)

Formula in Cell B2 Table 3:

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

Fill down and right

Ian.

• Level 4 (3,625 points)
Currently Being Moderated
May 8, 2013 4:04 AM (in response to Yellowbox)

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

• Level 4 (3,625 points)
Currently Being Moderated
May 8, 2013 6:15 AM (in response to Yellowbox)

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.

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.

• Level 4 (3,625 points)
Currently Being Moderated
May 8, 2013 6:21 AM (in response to Yellowbox)

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

=COUNTIF(B2:B9,"TRUE")

and fill to the right.

Ian.

• Level 4 (3,625 points)
Currently Being Moderated
May 8, 2013 8:06 PM (in response to witmac)

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.

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.