Use Excel to compare two lists of data

Applies to: Microsoft Office Excel 2003

For financial record-keeping, you often have to compare two lists to
find data that appears on one but not on the other. Which new clients
need to be added to the master database? Which credit card receipts are
not reflected on the monthly statement, and vice versa? What checks and
deposits are still outstanding? It’s a tedious task if you do it
manually.

Microsoft Office Excel 2003 can help you perform this task faster
and more accurately. In many cases, you can do it even if the data is
not provided in a standardized form in both lists.

Comparing two lists of assets

In this article, you’ll use data from the Compare Two Lists sample datasheet to learn how an accountant could use Excel functions to compare two lists of assets β€” the fixed asset records for a company and a property tax affidavit that must be updated annually. It’s a five-step process:

  1. Make sure each asset has a unique identifier.
  2. Find assets on the property tax affidavit that are not in the fixed asset records.
  3. Find assets in the fixed asset records that are not on the property tax affidavit.
  4. Sort lists to see nonmatching assets more easily.
  5. Review nonmatching assets and check for accuracy.

Step 1: Make sure each asset has a unique identifier

The following illustrations show the two lists that you want to
compare β€” a list of assets from a property tax affidavit and a list of
fixed asset records.

Business assets on property tax affidavit and on fixed asset records

Property tax affidavit and fixed asset records

As you see, the property tax affidavit does not have unique
identifiers, such as asset numbers, for each asset. But to compare
these lists, assets in each one must have a unique identifier.

In this example, the best way to identify each asset is by a
combination of asset description and date, so you’ll add a new column
called “Description & Date” to each list.

Note In the Compare Asset Lists sample
datasheet, the property tax affidavit and the fixed asset records have
been copied onto one worksheet.

Add a “Description & Date” column to the fixed asset records

  1. In the Fixed Asset Records area of the worksheet, select the Asset column,
    and then on the Insert menu, click Columns.
  2. In cell G2, type Description & Date, and then press ENTER.
  3. Select cell G2.
    On the Format menu, click Cells, click the Alignment tab, select Wrap text, and then click OK.
  4. In cell G3, type =H3&” “&YEAR(I3). This combines the description from cell H3 and the year from cell I3 into one field.
  5. To copy the formula from cell G3 to cell G14, select cell G3 and then drag the fill handle Fill handle to cell G14.
  6. To make the column width fit the results, double-click the boundary to the right of the Description & Date column heading.

Fixed asset records with combined description and date in new column

Fixed asset records with combined description and date

Add a “Description & Date” column to the property tax affidavit

  1. In the Property Tax Affidavit area of the worksheet, select the column to the right of the Cost column, and then on the Insert menu, click Columns.
  2. In cell E2, type Description & Date, and then press ENTER.
  3. Select cell E2.
    On the Format menu, click Cells, click the Alignment tab, select Wrap text, and then click OK.
  4. In cell E3, type =C3&” “&B3.
  5. To copy the formula from cell E3 to cell E12, select cell E3, and then drag the fill handle Fill handle to cell E12.
  6. To make the column width fit the results, double-click the boundary to the right of the Description & Date column heading.

Property tax affidavit with combined description and date in new column

Property tax affidavit with combined description and date

Now that you’re comparing apples to apples, you can get down to business.

Step 2: Find assets on the property tax affidavit that are not in the fixed asset records

  1. In the Property Tax Affidavit worksheet, select the column to the right of the Description & Date column,
    and then on the Insert menu, click Columns.
  2. In cell F2, type Missing?.
  3. In cell F3, type =ISNA(MATCH(E3,$I$3:$I$14,FALSE)).
  4. To copy the formula from cell F3 to cell F12, select cell F3, and then drag the fill handle Fill handle to cell F12.

Assets that are on the property tax affidavit but not in the fixed asset records will display TRUE in the Missing? column.

New
Missing? column showing whether an asset is not found in other list

New Missing? column

Step 3: Find assets in the fixed asset records that are not on the property tax affidavit

  1. In the Fixed Asset Records worksheet, in cell M2, type Missing?.
  2. In cell M3, type =ISNA(MATCH(I3,$E$3:$E$12,FALSE)), and then press ENTER.
  3. To copy the formula from cell M3 to cell M14, Select cell M3, and then drag the fill handle Fill handle to cell M14.

Assets that are in the fixed asset records but not on the property tax affidavit will display TRUE in the Missing? column.

Step 4: Sort lists to see nonmatching assets more easily

To more easily see the assets that are not on both lists, you can sort the lists.

Note If you sort the lists without first
following these steps to convert the formulas to values, the formulas
will not calculate correctly. In a large database, converting the
formulas to values can also help avoid long recalculation times.

Sort for nonmatching assets in the Property Tax Affidavit worksheet

  1. Drag the pointer to select cells A2 to F12.
  2. On the Edit menu, click Copy.
  3. On the Edit menu, click Paste Special.
  4. In the Paste Special dialog box, under Paste, click Values, and then click OK.
  5. On the Data menu, click Sort.
  6. Under My data range has, click Header row; in the Sort by box, click Missing?, and then click OK.

Sort for nonmatching assets in the Fixed Asset Records worksheet

  1. Drag the pointer to select cells H2 to M14.
  2. On the Edit menu, click Copy.
  3. On the Edit menu, click Paste Special.
  4. In the Paste Special dialog box, under Paste, click Values, and then click OK.
  5. On the Data menu, click Sort.
  6. Under My data range has, click Header row; in the Sort by box, click Missing?, and then click OK.

You can now easily see which assets may need to be added or deleted to update the property tax affidavit.

Property tax affidavit and fixed asset records sorted by
Missing? column

Property tax affidavit and fixed asset records sorted by Missing? column

Step 5: Review nonmatching assets and check for accuracy

Before you update the property tax affidavit, it’s a good idea to
manually review the nonmatching assets β€” which is fortunately a lot
faster than manually comparing the entire list. Review the nonmatching
assets and determine why they don’t match. Were the assets retired or
sold? Are they new assets that should be added, or are they assets that
are not required to be reported on the affidavit? Or did the assets
fail to find a match because of misspellings or other errors?

Other ways to prepare data for matching

Sometimes the data in two lists must be standardized before Excel
can compare the lists. In the preceding example, the dates in one list
were converted to years, and two fields were combined into one. The
following table shows common data inconsistencies and suggested Excel
functions that you can use to prepare data for matching.

Problem Example How to modify
Inconsistent abbreviations Litware, Inc.
vs. Litware, Incorporated
Use the LEFT function to create a field with only the number of characters from the left that you specify.
Extra numbers or characters on one list 12345 vs. 12345-1033 Use the LEFT or RIGHT functions to create a field with only the number of characters from the left or right that you specify.
Imprecise numbers caused by estimates, rounding variances, or sales tax $12,000 vs. $12,011 Use the ROUND function to create a field with comparable amounts.
Extra spaces Jae B. Pak vs. Jae B. Pak Use the TRIM function to remove all spaces from text except single spaces between words.


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: