Compare two columns and formate based on condition

Question

Level 1

13 points

Compare two columns and formate based on condition

I know this dead horse has been beaten and I've read my fair share of threads and manuals to no avail.. I have two list that consist of movie titles, holiday movies to be exact. I'm creating a holiday movie schedule which consist of three(3) columns...A,B and C. Column A is the Date, Sat, November 17 2012 thru Monday, Dec 24, 2012. Column C consist of a movie list divided into 2 sections with three subsections each. Section 1 is animated movies and Section 2 is live action, each subsection, 1.1, 1.2, 1.3, 2.1, 2.2 and 2.3 are lists based on popularity with the kids...low, medium and high respectively. And finally column B is the movie list relative the column A...the schedule.

As I write a movie title in column B, I'd like the cell fill to be light red and the corresponding title in column C change to strike through font type. This way I know I've added the movie title to the schedule...this comes in handy when I ask the kids to help so we have no duplicates in the schedule. I'm assuming this would take a combination of; Conditional Formatting, cell formulas and perhaps an additional blank column for trigger results.

I'm including the table, which include an experimental column I was working on. As an FYI, this has been completed in Excel already, just hoping to get it done in Numbers.

Thanks for any help anyone can give.

Date	Movie Name	Class
Sat, Nov 17, 2012		*Animated Christmas Movies*	TRUE
		Lowest Priority Animation	TRUE
		Frosty Returns	TRUE
Sun, Nov 18, 2012	The Nightmare Before Christmas	Rudolph and Frosty's Christmas in July	TRUE
		Rudolph the Red-Nosed Reindeer & the Island of Misfit Toys	TRUE
		Rudolph's Shiny New Year	TRUE
Mon, Nov 19, 2012	Nothing Like the Holidays		TRUE
		Medium Priority Animation	TRUE
		Jack Frost Animation	TRUE
Tue, Nov 20, 2012	Home for the Holidays	It's Christmas Time Again, Charlie Brown	TRUE
		Christmas in South Park	TRUE
		Cartoon Network Christmas Rocks	TRUE
Wed, Nov 21, 2012	Planes, Trains and Automobiles	Cartoon Network Christmas Yuletide Follies	TRUE
		Cartoon Network Christmas Vol3	TRUE
		Twas the Night Before Christmas	TRUE
Thu, Nov 22, 2012	Planes, Trains and Automobiles	The Little Drummer Boy	TRUE
			TRUE
		Highest Priority Animation	TRUE
Fri, Nov 23, 2012	Trapped in Paradise	The Simpson's Christmas	TRUE
		A Very Special Family Guy Freakin' Christmas	TRUE
		Family Guy: Road To The North Pole	TRUE
Sat, Nov 24, 2012		American Dad! The Most Adequate Christmas Ever	TRUE
		A Charlie Brown Christmas	TRUE
		The Nightmare Before Christmas	FALSE
Sun, Nov 25, 2012	Die Hard	Frosty the Snowman
	Die Hard 2	Hooves of Fire
		How the Grinch Stole Christmas
Mon, Nov 26, 2012	Gremlins	Santa Claus is Comin' to Town
		The Year Without a Santa Claus
		Rudolph, the Red-Nosed Reindeer
Tue, Nov 27, 2012	The Ice Harvest
		*Live Action Christmas Movies*
		Lowest Priority
Wed, Nov 28, 2012	Reindeer Games	National Lampoon's Christmas Vacation 2: Cousin Eddie's Island Adventure
		Chasing Christmas
		The Nativity Story
Thu, Nov 29, 2012	Bad Santa	Unaccompanied Minors
		Jingle All the Way
		Jack Frost Live
Fri, Nov 30, 2012	The Shop Around the Corner	The Santa Clause 3: The Escape Clause
		The Santa Clause 2: The Mrs. Clause

Sat, Dec 1, 2012	The Bishop's Wife	Medium Priority	0
		Bad Santa	Bad Santa
		Mixed Nuts	Mixed Nuts
Sun, Dec 2, 2012	The Chronicles of Narnia: The Lion, the Witch and the Wardrobe	Reindeer Games	Reindeer Games
		The Ice Harvest	The Ice Harvest
		The Shop Around the Corner	The Shop Around the Corner
Mon, Dec 3, 2012	Miracle on 34th Street B&W	The Bishop's Wife	The Bishop's Wife
		Christmas in Connecticut	Christmas in Connecticut
		The Chronicles of Narnia: The Lion, the Witch and the Wardrobe	The Chronicles of Narnia: The Lion, the Witch and the Wardrobe
Tue, Dec 4, 2012	Mixed Nuts	Nothing Like the Holidays	Nothing Like the Holidays
		Home for the Holidays	Home for the Holidays
		The Family Man	The Family Man
Wed, Dec 5, 2012	Scrooged	Miracle on 34th Street 1994	Miracle on 34th Street 1994
		Miracle on 34th Street B&W	Miracle on 34th Street B&W
		Just Friends	Just Friends
Thu, Dec 6, 2012	Just Friends	Trapped in Paradise	Trapped in Paradise
			0
		Highest Priority	0
Fri, Dec 7, 2012	Miracle on 34th Street 1994	Die Hard	Die Hard
		Die Hard 2	Die Hard 2
		Gremlins	Gremlins
Sat, Dec 8, 2012		How the Grinch Stole Christmas Live Action	How the Grinch Stole Christmas Live Action
		The Abbot and Costello Christmas Show	The Abbot and Costello Christmas Show
		A Very Harold & Kumar Christmas	A Very Harold & Kumar Christmas
Sun, Dec 9, 2012	Christmas in Connecticut	Four Christmases	Four Christmases
		Fred Claus	Fred Claus
		Deck the Halls	Deck the Halls
Mon, Dec 10, 2012	How the Grinch Stole Christmas Live Action	Christmas with the Kranks	Christmas with the Kranks
		Santa Claus: The Movie	Santa Claus: The Movie
		Home Alone	Home Alone
Tue, Dec 11, 2012	The Family Man	Merry Christmas	Merry Christmas
		A Christmas Carol Animated	A Christmas Carol Animated
		National Lampoon's Christmas Vacation	National Lampoon's Christmas Vacation
Wed, Dec 12, 2012	The Abbot and Costello Christmas Show	We’re no Angels	We’re no Angels
		Holiday Inn	Holiday Inn
		White Christmas	White Christmas
Thu, Dec 13, 2012	Four Christmases	Scrooged	Scrooged
		Planes, Trains and Automobiles	Planes, Trains and Automobiles
		The Polar Express	The Polar Express
Fri, Dec 14, 2012	Santa Claus: The Movie	Elf	Elf
	Fred Claus	Surviving Christmas	Surviving Christmas
		It's a Wonderful Life	It's a Wonderful Life
Sat, Dec 15, 2012	Home Alone	The Santa Clause	The Santa Clause
	Merry Christmas	A Christmas Carol Live Action	A Christmas Carol Live Action
		Miracle on 34th Street Color	Miracle on 34th Street Color
Sun, Dec 16, 2012	A Christmas Carol Animated	A Christmas Story	A Christmas Story
	Deck the Halls		0
		Harry Potter and the Sorcerer's Stone	Harry Potter and the Sorcerer's Stone
Mon, Dec 17, 2012	National Lampoon's Christmas Vacation	Harry Potter and the Chamber of Secrets	Harry Potter and the Chamber of Secrets
		Harry Potter and the Prisoner of Azkaban	Harry Potter and the Prisoner of Azkaban
		Harry Potter and the Goblet of Fire	Harry Potter and the Goblet of Fire
Tue, Dec 18, 2012	Holiday Inn	Harry Potter and the Order of the Phoenix	Harry Potter and the Order of the Phoenix
		Harry Potter and the Half-Blood Prince	Harry Potter and the Half-Blood Prince
		Harry Potter and the Deathly Hallows: Part I	Harry Potter and the Deathly Hallows: Part I
Wed, Dec 19, 2012	We’re no Angels	Harry Potter and the Deathly Hallows: Part 2	Harry Potter and the Deathly Hallows: Part 2


Thu, Dec 20, 2012	Christmas with the Kranks


Fri, Dec 21, 2012	White Christmas
	The Polar Express

Sat, Dec 22, 2012	Elf
	Surviving Christmas
	A Very Harold & Kumar Christmas
Sun, Dec 23, 2012	Miracle on 34th Street Color
	The Santa Clause
	A Christmas Carol Live Action
Mon, Dec 24, 2012	It's a Wonderful Life
	A Christmas Story

Tue, Dec 25, 2012


Wed, Dec 26, 2012	Harry Potter and the Sorcerer's Stone
	Harry Potter and the Chamber of Secrets
	Harry Potter and the Prisoner of Azkaban
Thu, Dec 27, 2012	Harry Potter and the Goblet of Fire
	Harry Potter and the Order of the Phoenix
	Harry Potter and the Half-Blood Prince
Fri, Dec 28, 2012	Harry Potter and the Deathly Hallows: Part I
	Harry Potter and the Deathly Hallows: Part 2

Sat, Dec 29, 2012


Sun, Dec 30, 2012


Mon, Dec 31, 2012


Tue, Jan 1, 2013

Posted on Nov 11, 2012 4:02 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Nov 12, 2012 6:15 PM in response to seebeasy

My first thought was an auxiliary table placed behind the original table, with the original made transparent to allow the second, conditionally formatted, table to show through.

The problem with this approach was the 'wrap' setting on the original table, which made for some difficulty in keeping the row heights the same for both tables.

So I opted for a second approach, shown in the view of a section of your table below:

Column C (yellow) is the original title list. After entry, it may be hidden.

Column D uses the formul shown above trhe table (copied below) to

copy the contents from column C if the item has not been scheduled
copy the contents from column C and prefix a period if the item has been scheduled
insert a null string where the cell in column C is empty

The conditional format rule, applied to all cells in column D, fills the cell with red and strikes through the text if text in the cell begins with a period.

Formula:

D2: =IF(LEN(C)>0,IF(COUNTIF($B,C)>0,"."&C,C),"")

Fill down to the end of column D

Regards,

Barry

Reply

Answer 2

seebeasy Author

Level 1

13 points

Nov 13, 2012 5:56 PM in response to Barry

Barry - Thanks, that worked out just fine... Now I'm working on highlighting the movie title in column B light red once It's added and it finds the duplicate in column C...it's proving to be a challenge as well. I'm then going to attempt to highlight the cells in column A that are equal to either Fri, Sat or Sun...on those nights I can schedule several movies as opposed the the week days which we ussually get to watch one.

Stephen

Reply

Answer 3

Barry

Level 9

59,861 points

Nov 13, 2012 9:19 PM in response to seebeasy

Hi Stephen,

Both of these are solvable, and the Date solution cold be similar to that used for the 'stock list' in column C. The Movie Title solution is a different case, though.

The stock list is edited only occasionally, so requiring the user to unhide column C, add a title, then rehide the column is workable.

The same is true of the date list, so this too would work with the dates entered into a column that will be hidden, then copied into a visible column with a formula that introduced a detectable difference into the copied version, dependent on the weekday of each date. That difference would be used to trigger the conditional formatting of the cell containing the calculated date as text value. See the example below.

But the (scheduled) Movie Title column doesn't fit nicely into that mode of operation, as it is edited quite often. As editing, usng the model described, requires making the data column visible, editing the entry (or entries) in that column, then rehiding the column, the hassle factor soon becomes insufferable, if nothing else.

The first solution I considered (see earlier reply) is workable only if the the formatted table starts and remains the same size in all details (ie. individual row height) as the main table.Any change in row height in the main table not reflected in the auxiliary table immediately destroys the impression of formatting applying to the correct cells. With cells set to wrap text and varying lengths of movie titles, wrapped lines changing the height of individual rows is pretty much unavoidable.

Best practice here would seem to be to use a separate column to flag the titles already in the "Class" list with a "√" (or flag those not in the list with an "X"), and use conditional formatting to change the background colour of the flagging cell.

Here's a sample, using "W" to flag the weekend dates in the new column B, and "√" to flag the titles that are on the list already in the new column D.

I've added two titles to the weekend date, November 18. One is non-existent (as far as I know), so it doesn't appear in the list and doesn't get flagged; the other is chosen from a visible part of the list to show the highlighting of the title in that list. Column "C" (now column "F" due to the insertion of two new columns) is hidden. G, labelled .Class, is the calculated column (D) from the previous message.

Regards,

Barry

Reply

Answer 4

seebeasy Author

Level 1

13 points

Nov 14, 2012 6:18 PM in response to Barry

Hi Barry -

Again, thanks for the great ideas. The solution I used was based on your original idea of using an aux table. Iformated the table without text wrapping, modified some of the original code as well as a bit of my own...it looks like this now...see image at bottom.

The dates were done using Conditional formatting, "If text starts with" "Fri" fill cell RED...same for Sat and Sun.

Getting the "Schedule", column B, to highlight was fun, I used the aux table(Table 4) behind Column B of Table 1, formated both columns as NO FILL so they're transparent and added this formula, =IF(LEN(Table 1 :: B)>0,".",""). This put a "." in every cell that had a movie title in the corresponding Table 1 Column B cell. I then used conditional formatting, "If text is" . highlight cell light red.

My only remaing issue is, when I type a movie title in column B that's not in the master list, column C...it lets me put it in. I'd like a way to validate the title in column B against C, in other words...If I mistype or add a movie title that doesn't exist in column C...do something. As you mentioned earlier, something like this is difficult as column B is heavily changed...it's too bad Numbers didn't use formulas in conditional formatting.

Reply

Answer 5

seebeasy Author

Level 1

13 points

Nov 14, 2012 7:31 PM in response to seebeasy

Barry, well...I have it doing everything I want. I'll break it down for those looking to do the same or similar.

Parts included:

2 Tables

3 Formulas

3 Conditional Formats

CF = Conditional Formatting

T = Table

C = Column

T1 has 5 columns, A - E. A has Date and CF, B has Scheduled Movie Title, C has Master Movie List, D has formula and CF, E has formula.

T2 has 1 column, A, which has formula and CF. This table is the same size as Table 1, Column B...and place behind column B. Both table a set to no fill so they're transparent.

Column T1CA's CF is "Text starts with" "Fri" fill cell RED...do the same for "Sat" and "Sun"

Column T1CD's formula is =IF(LEN(C)>0,IF(COUNTIF(B,C)>0,"."&C,C),""), fill down..., and the CF is "Text starts with" "." set font to strike through."This is thanks to Barry"

Column T1CE's formula is =IFERROR(VLOOKUP(B,C,1,FALSE),"")

Column T1CA's formulas is =IF(LEN(Table 1 :: E)>0,"","*") and the CF is "Text doesn't contain" "*" fill cell LIGHT RED, and "Text contains" "*" set font to WHITE

When done, hide column C and E from table 1.

Lotsa thanks to Barry and all the other people who's solutions I read to other questions...they gave me some good ideas.

Seebeasy

Reply