Skip navigation

Compare two columns and formate based on condition

3122 Views 5 Replies Latest reply: Nov 14, 2012 7:31 PM by seebeasy RSS
seebeasy Calculating status...
Currently Being Moderated
Nov 11, 2012 4:02 PM

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

 

 

 

 

 

 

 

 

 

 

 

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    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:Picture 2.png

    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

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    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.

    Picture 3.png

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

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.