Any Excel Formatting Experts Here? (DVD Profiler CSV Export Question)
#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:
...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!
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
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!
#2
DVD Talk Hall of Fame
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.
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.
#3
DVD Talk Gold Edition
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")
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")
#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!
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!
#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?
#6
Senior Member
#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.
#8
DVD Talk Gold Edition
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.
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.