Useful Excel Functions

By | 2013/07/17

Perhaps you’ve had a long-term relationship with Excel. If so, we have that in common. I’ve used Excel for years — decades, actually, but I don’t consider myself an Excel guru. I have a wonderful long-time consultant I call on for serious Excel work (THAT GUY is a guru!). However, on a day-to-day basis, I find myself using Excel again and again to solve manifold (vocab word for today) challenges.

Often, manipulating data in Excel is flat-out easier than working directly with the database.  Here are a few real-life examples you may find helpful.

Matching up Data from Two Different Sources

In cases where two reports exist and you need a bit of information from each report, Excel can really shine.

Suppose you have a spreadsheet of purchase orders that came from a user (works just as well with invoices, customers – basically anything with an identifying field like an ID#). In this example, the user was tracking purchase orders placed by his department and he wanted to confirm payment date and payment amount from the ERP. His spreadsheet had stuff in it that the ERP didn’t have, and visa-versa.

Here are the 1st four columns from the user’s spreadsheet:

User Spreadsheet

User's Spreadsheet

The second spreadsheet came from the ERP system and it shows actual payment dates and amounts:

ERP Spreadsheet

ERP Data in Spreadsheet form

Here’s the goal: You want to match up the data, so that the manual spreadsheet also includes the actual ERP payment date and amount.

Requirement: You need at least one identifying field/ID# that matches on each spreadsheet. In this example, it’s the purchase order number, found on columns D (user spreadsheet) and B (ERP).

VLOOKUP is the function we’ll use. Here are 2 VLOOKUPs we can add to the user’s spreadsheet to pull in the ERP info:

To lookup the payment date: =VLOOKUP(D2,’ERPsheet’!$B$2:$F$2329,4,FALSE)

To lookup the payment amount: =VLOOKUP(D2,’ERPsheet’!$B$2:$F$2329,5,FALSE)


It’s fairly self-explanatory, but here’s a quick run-down of the VLOOKUP parameters:

  • D2 is the PO# from the user’s original spreadsheet –  this is what you’re looking up in the ERP data.
  • ‘ERPsheet’!$B$2:$F$2329 is the ERP data (we’re ignoring column A because it isn’t necessary). I put $ in place to prevent the cells from shifting as we copy the formula.
  • The numbers 4 and 5 represent the column numbers we want to return from the ERPsheet table when we get a hit.
  • FALSE means we want an exact match. VLOOKUP will return #N/A if the PO# isn’t found.

Replacing VLOOKUP’s #N/A with zero

There are times when you want to replace the VLOOKUP #N/A with something more meaningful. For example, when returning amounts, it’s often more desirable to default to zero. Here’s a trick that will default a VLOOKUP to 0 if no match is found:

=IF(COUNTIF(‘Lori-AFS’!$B$2:$F$122,D9),VLOOKUP(D9,’Lori-AFS’!$B$2:$F$122,5,FALSE),0)

Using Filter to Remove Headings in Text Files

At my current job, many reports are created as text files, with headers inserted every 50 lines or so. When you bring these files into Excel, they are difficult to manipulate because of all the junk. For example:

Legacy Text Report

Text Report from Legacy System

For the report above, you need to import the data as a fixed-length file type. You’ll have to add column separators based on the headers on the report (I’ll assume you have this covered). After it’s in excel, select the entire data range and click ‘data’, ‘filter’.  Click the down arrow on column A and deselect ‘(select all)’. Now select only those rows you wish to remove. On the example above, you want to select anything that looks like a heading, anything beginning with ‘total’ and all blanks.

Excel filter

Excel Filter

When excel returns back the filtered results, confirm you don’t want any of it and right-click ‘delete rows’.

If you selected correctly, you should end up with a nice list of formatted data.

All of the extraneous heading and text should be gone.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.