Monday, February 26, 2007

UPC Barcode Generator

Here's a recent $50 Project I completed for a client: a simple, straightforward UPC barcode image generator. Now, many of you might be groaning, thinking, "Come on, Excel_Geek, there are hundreds of barcode add-ins and software packages out there that work with Excel." And you're right. What's wrong with them? Nothing really. This client simply wanted his own ultra-simple Excel-based solution -- something he could input a number into and get an image he could use.

Here's a look at what I built:

(Click on the image above to open it larger in another window.)

How's it work? Basically I created a range containing cells with very narrow column widths and used conditional formatting to change the background color of the cells to either black or white, depending upon the value in the cells (either "TRUE" or "FALSE") which I obtained using VLOOKUP formulas to grab the appropriate value for each digit in the code from a table containing them.

Then once the "live" version of the barcode is set, I use Excel's little known "camera" feature (copy a range then hold down shift when selecting the Edit menu and you'll find it -- this feature is gone in Excel 12 :-( ) to copy the range containing the barcode and paste it as a copy-pastable image.

It was remarkably simple once I understood how UPC barcodes work.

To my Excel_Geek Insiders (who'll be receiving this file soon as part of your subscription), unhide the hidden rows if you really want to to see the "guts" of how this works.



Thursday, February 08, 2007

Comparing Phrases - Google Adwords Application

I just completed a $50 Project for a client who wished to compare a list of phrases to see if they appeared in another list, only the thing was it didn't matter what order the words in the phrases were.

So...if the phrase "big boy eats cake" was in the second list, "boy eats big cake" would result in a match. What is important is that both phrases contain exactly the same more no less, but the order is irrelevant. I believe the application for this type of function was to make sure that a list of keyword phrases, say for a Google Adwords campaign, didn't contain any phrases that would be "duplicates" for that purpose.

Using formulas to parse out the individual words in the phrases and a little bit of VBA code, I think I've come up with a simple-ish solution to this little problem.



Sunday, February 04, 2007

Okay...Who's Ready for a New NCAA Basketball March Madness Bracket?

I can't tell you how many requests I get, from close friends and family, as well as from readers around the United States and internationally, for an Excel-based NCAA men's basketball March Madness tournament bracket. Well, here's a pretty neat one I whipped up just in time for the tournament.

The workbook consists of three sheets:

1) The "Setup & Actual Results Bracket" worksheet is where you input the teams who've made the tournament, record the results of the games as they are played, and also where you can set how many points each correct pick is worth by round. The image below is a glimpse of this worksheet (well, at least the upper left quarter of it). Don't worry, this is laid out so that you can nicely print the entire bracket on one 8.5" x 11" page.

(click on the image to open it up larger in a new tab/window.)

2) The "My Picks & Points Bracket" worksheet is where you would predict the winners of each game. Using data validation, I've managed to automatically make each cell into which you make a pick only allow the teams playing in that game. Then, using conditional formatting, I've made it so that correct picks (once you've input the actual results in the other worksheet) show up as green, and incorrect picks show up as grey, AND eliminated teams that you've picked to win subsequent games will also show up grey. Finally, this sheet contains an up-to-date total of the points you've won, as well as the total possible points still remaining based upon your picks.

(click on the image to open it up larger in a new tab/window.)

3) The "My Points Chart" worksheet contains an in-cell tornado chart (I admit it: I've got a thing for these crazy charts!) displaying round-by-round performance.

(click on the image to open it up larger in a new tab/window.)

Now, the way I've set up this file, it's pretty much self-contained for a single person to track their picks and points, but I can certainly envision using this as the basis for a complete system in which one "master file" is created to track the results of the actual games as the are played and to set the number of points awarded for correct picks in each round. Then any number of "player files" are created and emailed to each person to make their picks and email back to the person managing the system. Then the "master file" could simply reference the results in each "player file", displaying a summary or a leaderboard, if you will. Could be pretty cool.

To my growing Excel_Geek Insiders subscribers, enjoy this's on it's way. To those of you who haven't subscribed yet, did you know that for only $39.95 per year, you get copies of all Excel_Geek $50 Projects I do for the year, as well as copies of these special little projects I just decide to do? It's true. Sign up today!

Enjoy the tournament!


UPDATE: I released this file for download. See comments here. Download the file here.