Allan and Wilson books is going to be presenting information about their new titles to distributors and book stores all around the country. Read how PowerMerge and QueryCell help them do it quicker, easier and with less mistakes.

My name is Jenny Malhouse and I’m the general IT manager at Allan and Wilson books. When I say IT Manager I mean manager, developer, administrator, innovator and dogsbody. Since we are a small ( but growing ) company everyone has to pitch in where they can. I find myself handling most of the computer issues that crop up.

Over a few months our editors are going to be traveling around the country presenting our new releases to resellers and book stores. I’ve been asked to email out our New Books This Season PowerPoint presentation in advance.

I’ve been given a list of email addresses but it’s not going to be that simple!

We only want to send the presentation to partners in cities that are going to be hosting an editor event. If we sent the PowerPoint presentation to the rural stores that our editors are not going to get time to visit we might cause unnecessary offence.

Some of our partners are only going to be interested in certain categories of our range. There is no point in sending the slides on romantic fiction to the military history book store in Boise Idaho.

The list of Partners, Events and Categories are all in separate Excel worksheets, I could try and sort it all out by hand but luckily I’ve just installed QueryCell, an Excel Add-In that lets me use SQL inside Excel, it’s really going to speed things up.

A screenshot of some of our partners and their categories of interest in Microsoft Excel

A screenshot of our book categories in Microsoft Excel

A screenshot of our list of events

I will need a list of all the partners who are in a region that will be hosting an event and the categories they are interested in.

Once again I could manually look through the data, copying and pasting information as I find it but it’s going to be a lot simpler to just use a simple SQL query and QueryCell.

A simple SQL query returns the email addresses of all the partners near an event and their categories of interest

The query we ran :

SELECT distinct
   a.NAME,
    A.Region,
    B.Category
FROM
    a, b, c
WHERE
    a.NAME = B.NAME AND
    a.REGION = C.Region

Great, now I have a list of the partners near events and the book categories they are interested in!

Since all my querying has taken place inside Excel using QueryCell I still have the all the standard Excel functionality available at the click of my mouse.

I am going to use an Excel Pivot table to reorient my data and make it easier to deal with.

Instead of having all my book Categories listed in a column I now have them as a single row, with a count of 1 indicating that the listed partner is interested in that category.

With a small amout of manual clean up and formating my table of data is ready to be used to generate customized powerpoint presentation.

The data is now ready for PowerMerge to create my customized emails!

Before I jump into PowerMerge and produce my customized power point presentations I am going to do one more thing in Excel.

Janette our editorial communications coach has asked for a list of all the partners that will be receiving a presentation and are interested in Inspirational or Mystery books.

She just needs a list of their names so she can give them a call and pass on some special information from our suppliers.

I could look through the table of data I have just produced manually but using querycell I can get that information easily in seconds.

I check the ‘Mark Results’ checkbox in QueryCell. This tells QueryCell that I do not want all the data matching my query returned, instead I simply want the matching rows colored.

I then write a very simple query to select only those rows where the Inspirational column or the Mystery column contains a 1.

Immediately I see the rows I am interested in. It’s now a snap to select those names and email them to Janette.

Now I can return to generating my PowerPoint presentations.

See next post