Getting Unique Values Only

Often times when you are working with Excel, you want to reduce a list of items so that only the unique instances are listed. In other words, if, within a column or list an item happens to be duplicated, you want that item to appear only once. Maybe you have a mailing list, for example, and fear that some customers may be entered more than once. Or perhaps you’ve been collecting emails and are now ready to send out promotional or “Thank you” emails, but don’t want to spam people with multiple messages.

Luckily, Excel makes it very easy to do this, saving you from yourself. In fact, there are two ways. One is simpler and built in to Excel’s multiple functionalities. The other is a bit more difficult and less intuitive, but is more flexible and dynamic. I explain them both to give you flexibility in dealing with these types of business problems. For my example, I use a simple list of animals where a couple of them (cat, bird) are each repeated.

Note: This tip is for Excel 2007, but is applicable, with a tweak or two, with other versions of Excel.

Method 1:

  1. Select data from which you want to retrieve only the unique values. Underline, or otherwise differentiate, the header so that Excel can recognize it as such.
  2. Under the “Data” Ribbon, select the “Advanced” Option.
  3. Under Action, select “Copy to another location.”
  4. The “List Range” should be populated with the data set selected in Step 1
  5. Choose the same range for the “Criteria Range” (I normally copy and paste) from above (the “List Range”)
  6. Check the box next to “Unique Records”
  7. Hit the “OK” button Read more of this post

Keep Choppin’ and Keep Steppin’

Chop big or chop small. Chop quickly or chop slowly. But whatever you do, Keep Choppin’!

Not long ago I was going through one of my crazy circuit training routines when one of the employees at the gym looked at me with a peculiar, perplexed, almost confused face. She’s seen me there dozens of times—always friendly and conversant—so I was wondering why she was looking at me in such a manner. Her confused look was actually, well, confusing me a bit. Later she walked by and said “Why do you work so hard? Is it like your thing or something?” I didn’t really know how to respond. As odd as it sounds, I initially felt the way I did when I was in high school, the same way a lot of kids, struggling to find an identity, to fit in yet stand out, feel. I thought back to a time when doing the best you could in school meant you were being a “teacher’s pet”, “bookworm”, or worse yet, a “know-it-all”. I remember when my teammates called me “Coach’s Favorite” just because I did my best to be first in every sprint, first in the weight room, and last to leave the field after practice. Even as you get older, these labels don’t escape you. Young men and women who go to college are often accused by their less ambitious friends and classmates of “thinking they’re better” than them. Even wearing a suit to work, as I experienced firsthand, can create a stir and be frowned upon when those around you are more comfortable in Kakis and Polo Shirts.

So, I was actually unprepared to respond to Jo, the employee who had caught me off guard with her comment. I forgot what my response was. I probably said something I thought was funny like “I have to keep at it because the pool boy my wife hired is too handsome.” Or I may have brushed it off with “Nobody’s going to do it for me.” Whatever I said, I realized later the real reason. And unlike my normal witty and charismatic responses, it’s not very funny, clever, or necessarily inspiring or informative. It is however, law. It is a universal law that, while boring and simple, is one that few accept, let alone embrace.

I’ve gotten to where I am, you see, not by large jumps or progressions. Whether as a student, a finance professional, a wanna-be programmer, a father and husband, or a fitness junkie (and sometimes coach), it’s always taken (actually taking) some time to grow and learn. And I’ve always understood and accepted that. It was never—it is never—one big step. It is dozens, hundreds, even thousands of small ones. When you’re chopping a tree down, for example, you never know which chop ultimately brings it down. It’s a combination of all of them. When you study for a test, you can never pinpoint which hour or minute ultimately produces the outstanding score you receive. And it’s never one, two, or even three things that make someone fall in love with you. It’s the entire you. The whole is always more than the sum of the parts. Read more of this post

(E)Mail Merge

Blanca in Modesto, CA asked a very interesting question: Is there a way to perform a mail merge with email? In other words, Blanca was asking if it was possible for her to customize an email so that each person in her distribution list received the email customized to a level as highly personalized as she wanted?

The answer, as is usually the case—it seems almost impossible to find a business problem that the Microsoft Office Suite can’t handle, even if it involves advanced techniques such as VBA—is YES.

Here are the steps to take: Read more of this post

Money on memories!

Vacations are certainly not cheap. But the memories you create with your family are priceless!

When I was young–who am I kidding; even when I was not so young—I used to spend my (and more often than I care to admit, my parents’) money on material things. Whether it was cool jeans or the newest pair of “Jordan” sneakers when I was in high school and college or fancy rims for my car or cool electronics equipment when I was a little older; I never hesitated to spend money on “stuff”. Just “stuff”.

Well, whether it was because I was furthering my career in corporate accounting and finance and wanted to at least pretend to be responsible or because I got married, had children, and actually had to be responsible; I did a complete 180 with my approach to money, spending, and saving.  Rather than spend money as freely and carefree as I wanted and when I wanted, I began to deprive myself of almost any enjoyment or material ‘toy’, feeling that I was being selfish for spending on myself or, worse yet, that I hadn’t yet earned the right to reward myself.

Read more of this post

You fill in the blanks!

Figure 1: Neat data is not always "Analysis-Friendly" data

There are occasions when you will want to fill all empty/blank cells in a column. Often times, data is formatted in such a manner so that it is easier to read. Figure 1, for example, shows a portion of what an Accounting Report might produce for expenses over a given period of time. While it is very clean and easy to read, using this data to perform other analysis will require some work. If, for example, you wanted to use this data to show expenses by month or quarter, that would be difficult.

To make the data more “Analysis-Friendly”, then, the data needs to be converted to a “table” or “database” with records. To do so, we must make sure each row (or record) contains the expense (or similar category that is being grouped by the summary report). In our example file (attached), column B needs to contain the category for each row of data (date and amount).

Of course, we can do this the hard, manual way. We can retype the category for each subsequent row. No way! An easier way would be to copy the category heading and fill or paste in the subsequent rows. Better, but still some work.

There is actually an easier way.  Come on; of course there is! Read more of this post