Wednesday, April 30, 2014

Spreadsheet tip - Conditional formatting is good, but sometimes =IF() is better

In my last post, I mentioned that I recently worked on a big mail merge exercise producing 6000+ employee letters.

Well, as part of that exercise, one of the things I needed to do was merge data from a few other spreadsheets into one big worksheet. (I'll explain how I did that some other time.)

When merging spreadsheets, usually there is also a need to compare data and mark (or otherwise identify) differences in the data. Most spreadsheet applications provide conditional formatting as way to do this. Conditional formatting allows the user to mark or highlight cells based on content or formula rules. But, it is not always useful to use conditional formatting only, as there is often no easy way to filter data based on cell formatting.

(OK, yes, there is this Microsoft knowledge base article: https://support.microsoft.com/kb/213923 - but, I have to say it seems complicated compared to what I am going to explain next.)

A simple method I use is to insert an  =IF() formula in a separate column to give my spreadsheets something to filter. For example, when I've merged data from different worksheets, a simple thing I want to know (and filter on) is whether two cell values are the same. So the formula t use is something like:

=IF(A2=B2,"","X")

This translates to:

if Cell A2 is equal to Cell B2, then do nothing; else if Cell A2 is not equal to Cell B2, show "X"

After copying this formula down through all the rows I need to compare, I can set the spreadsheet to filter on "X", and show me all the cells where the data in Column A is not the same as the data in Column B.

=IF() formulas are very useful and versatile, and you can create a range of formula "rules" from simple to complex.

To beautify my spreadsheets I often combine conditional formatting with =IF() "rules" of this kind. They go well together, and very often the same =IF() formula can be used to create the conditional formatting.

Tuesday, April 29, 2014

Mail merge with LibreOffice - TIP: Make sure you are not using embedded fonts.

Recently, I was involved with a fairly big mail merge exercise. I had to create and print individual letters for 6000+ employees. No big deal, right? It's fairly standard mail merge project.

But, knowing that many employees will misplace or discard the letters and come seeking copies, I wanted to create PDF copies of each letter, stored on the company network, and with a unique file name comprised of inter alia the employee's cost center number, employee number, department name and job title. Again, no big deal but my outdated version of Microsoft Office wasn't going to do it, so I used LibreOffice 4.2.

It should have been plain sailing, but I experienced crash after crash, as LibreOffice just rebelled on both the Windows and Linux operating systems. And when it worked, it was SLOW.

Well, eventually I found the problem. It was small, and not something I would have thought of straight off, but with hindsight I guess it makes sense. MAKE SURE YOU ARE NOT USING EMBEDDED FONTS IN YOUR MAIL MERGE TEMPLATE!

Once I turned off embedded fonts, LibreOffice did its stuff quickly and efficiently. I was able to produce mail merges of all the letters in both the MS Word .doc format and PDF format. Printing worked smoothly, and the company's printing unit handled the inserting of the printed letters into window envelopes.

If you haven't tried it yet, you can check out LibreOffice here: https://www.libreoffice.org/

First post in a long time

This is my first blog post in a long, long time. I've played with blogs in the past but found I always ran out of things to say. It took me a while to realise that the reason for that is I'm always hiding myself from myself and others. Strange, isn't it?

Anyway, realising that it is far too easy to wear different masks for different occasions, I've finally decided to start a web site and a blog where I can share all the things that interest me, and where I can start to disclose more about myself over time.

Mostly the things that interest me are, well, geeky things. I'm into linux, spreadsheets, photography, books, meditation, thinking, and learning. And yes, it may seem contradictory, but the older I get the more I find I am into the mystical side of life, the universe and everything. The universe, or perhaps the multiverse, is vast - much vaster than most of us are able to comprehend.

I also have a Tumblr (http://grantstumblething.tumblr.com/) where I post my own photography, and share the photo's of others that I find interesting, or beautiful. And I've just started a personal web site (https://sites.google.com/site/grantthegeekspersonalpages/home) which is under construction at present.