Go Back  DVD Talk Forum > DVD Discussions > DVD Talk
Reload this Page >

Any Excel Formatting Experts Here? (DVD Profiler CSV Export Question)

DVD Talk Talk about DVDs and Movies on DVD including Covers and Cases

Any Excel Formatting Experts Here? (DVD Profiler CSV Export Question)

Old 11-25-08, 06:31 PM
  #1  
DVD Talk Special Edition
Thread Starter
 
Join Date: Sep 2002
Location: Around and about...
Posts: 1,400
Likes: 0
Received 0 Likes on 0 Posts
Any Excel Formatting Experts Here? (DVD Profiler CSV Export Question)

Like many orphaned DVD Spotters, I bit the bullet and bought DVD Profiler to keep tabs on the archives. Since DVDAF foolishly killed PDA support when they face-planted into v2.0, I was making a to-go copy for my PDA by copying the title column from my DVD Spot export and making a Word document that I could transfer to my PDA. One hassle was having to go in and manually color-coding the various HD/BD titles so I knew which I had upgraded.

Now that my collection is on DVD Profiler, I'm confronted with the same dilemma. DVD Profiler Mobile isn't an option because of the cost and frankly it's overkill. I just need a list of what I have when I'm at the store so I don't buy it over. (1500 titles + bad memory + g/f handling the storage = disaster waiting to happen!) I can export a CSV from DVDP with the title and format, but I'd ideally like a simple colored list of titles.

What I'd like to do is have some sort of IF/THEN conditional formatting of the CSV which can read the cell in the Media Type column and then apply color formatting to the title on that row, so that HDs would be red and Blus would be blue, so that this:

Code:
3 Extremes	DVD
3 Extremes II	DVD
3-Iron	DVD
30 Days of Night	Blu-ray
30 Rock: Season 1	DVD
300 (2006)	HD DVD
300	DVD
3:10 to Yuma	DVD
...would come out like this:

3 Extremes
3 Extremes II
3-Iron
30 Days of Night
30 Rock: Season 1
300 (2006)
300
3:10 to Yuma


The searching I've done on conditional formatting hasn't been very fruitful, thus my plea here. Hope it's not too off-topic. I figured there may've been others in the same boat and seeing as many are using Access to catalog their collections, this could be a simple problem for someone who actually knows what they're doing to solve.

Thanks in advance!
Old 11-25-08, 06:47 PM
  #2  
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 9,413
Received 81 Likes on 69 Posts
Slightly manual, but once you get the data in Excel, apply an autofilter, filter on Blu or HD, and color every row that hits the filter. A couple of steps, but much better than manually doing everything.

I don't know how to conditionally format one cell based on the contents of another cell, I'd like to know that as well.
Old 11-25-08, 07:13 PM
  #3  
DVD Talk Gold Edition
 
danwiz's Avatar
 
Join Date: Dec 2000
Location: Fairbanks, Alaska
Posts: 2,231
Received 101 Likes on 63 Posts
You can do it like this:

Question:
I want to do a conditional format to make cell A1 have red font if cell B1 is
equal to a certain value (text). Can I do a conditional format on a cell
referencing another cell?

Answer:
Yes. In A1 go to Conditional Formatting. Change the Cell Value is option to Formula is. Enter

=B1="Your Text"

Click Format, Font select the color you want. Click OK, OK.

I just tried this and it worked for me. FIRST: Select ALL of the cells where the data is listed.

For you, in the "formula" box use =B1:B1500="Your Text" (B1:B1500 is the cell range where it says "DVD", "Blu-ray" or "HD DVD")
Old 11-25-08, 07:30 PM
  #4  
DVD Talk Special Edition
Thread Starter
 
Join Date: Sep 2002
Location: Around and about...
Posts: 1,400
Likes: 0
Received 0 Likes on 0 Posts
Hey, that worked aces, dt! While not automatic, it's more than acceptable since it took less than 30 seconds to do them all.

Here are the steps I took for others' reference:

1. Open CSV in Excel and select a cell in the Media Type column.

2. On the menu bar, go Data>Filter>Autofilter and click it. A drop down menu arrow will appear on the column heading.

3. Select the format you wish to highlight (e.g. Blu-ray) and click it. Now, only the titles in that format will be visible.

4. Click the top of the Title column to select all titles and click the color chooser to select the color you wish to make those cells. (e.g. Blue. Duh.)

5. Now go and choose another format (e.g. HD DVD; HD DVD, DVD for combo discs) and notice that all the Title cells will automatically be selected. Color those. Rinse. Repeat.

6. Select (All) from the filter chooser list or go Data>Filter>Autofilter and uncheck the box to see all titles.

Copy and paste the title column to a Wordpad document and save to your PDA. I'm getting the lines for the rows and can't figure out how to lose them without losing the formatting, but no biggie.

While it's not "automatic" automatic, it's so quick to manually do this way, I'm satisfied. Kudos!
Old 11-25-08, 07:38 PM
  #5  
Needs to contact an admin about multiple accounts
 
Join Date: Jun 2008
Location: Medieval England, Iowa
Posts: 744
Likes: 0
Received 0 Likes on 0 Posts
I just downloaded DVD profiler for freea few days ago from their site. Whats this about "purchasing" it? Do i only have a trial version or something?
Old 11-25-08, 08:04 PM
  #6  
Senior Member
 
Join Date: Nov 2007
Posts: 425
Received 0 Likes on 0 Posts
Originally Posted by cinemaphile
I just downloaded DVD profiler for freea few days ago from their site. Whats this about "purchasing" it? Do i only have a trial version or something?
its was a trial you downloaded

Last edited by lacubs; 11-25-08 at 08:13 PM.
Old 11-26-08, 01:46 AM
  #7  
DVD Talk Special Edition
Thread Starter
 
Join Date: Sep 2002
Location: Around and about...
Posts: 1,400
Likes: 0
Received 0 Likes on 0 Posts
danwhiz's method doesn't work. It only highlighted the title ABOVE the title that was actually a Blu-ray. (e.g. Waking Life got colored blue when Walk Hard BD was the item below.) The Autofilter method works well enough.
Old 11-26-08, 06:43 PM
  #8  
DVD Talk Gold Edition
 
danwiz's Avatar
 
Join Date: Dec 2000
Location: Fairbanks, Alaska
Posts: 2,231
Received 101 Likes on 63 Posts
DirkBelig, kind sir. I beg to differ. The method I gave will work just fine if you set it up correctly. In fact, you made me question my own methods, so I just set up a spreadsheet with 50 DVD titles in it and some of them marked as DVD, some marked as HD DVD and some marked as Blu ray, applied my conditional formula and it worked just fine.

If it highlights the title ABOVE the actual Blu-ray then I assume that you made a typo in the formula.

By the way - you should learn how to be just a bit more diplomatic - you could have said something like "danwiz's method does not appear to work..." or "has anybody tried danwiz's method and had a problem with it?" You know what I mean.

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service -

Copyright © 2024 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.