Technology

Spreadsheets: Cleaning Out Old Records

Are old records gumming up new worksheets? Here's a quick &doublequot;match&doublequot; function you can use between worksheets to clean up new fil...
Bill JelenFebruary 3, 2011

Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Jeff M. wins an autographed copy of Guerilla Data Analysis Using Microsoft Excel from CFO and MrExcel.com for his question: “I have two similar databases of customer data in separate spreadsheets. Each record consists of a row with the same fields in each worksheet. The first worksheet is an older file, while the second file is newer and contains the records in the first file, plus hundreds more. I have identified records in the first worksheet, which must be deleted in the second worksheet. What’s the quickest and easiest way to do this without going through one by one to delete the records in the second sheet? (I don’t want to delete the records in the first worksheet.)”

Hopefully, Jeff has a unique key field such as customer number or record ID that is in both workbooks. If so, the problem will be relatively easy.

CFO Insights on Inflation, Workforce Challenges, and Future Plans 

CFO Insights on Inflation, Workforce Challenges, and Future Plans 

Download our 2022 survey report for a high-level view of finance team projections and strategies, directly from our CFO.com executive readers.

Start by sorting all of the records to be deleted together into one section of the original worksheet. If you cannot sort the data, make a copy of the worksheet and sort that data. You are trying to get a contiguous list of the customer numbers to be deleted.

Keep the first workbook open in the background. Open the second workbook. Add a new column heading called Match.

You will be using a formula that is similar to VLOOKUP, except this formula uses the MATCH function. The syntax will be something like this:
                          =MATCH(cell with customer number in second file, range of customer numbers
                          in the first file, zero).

Start building the formula in the first data row of the second workbook. Type:
                          =MATCH(

Using the mouse, click on the first cell containing a customer number in the first data row of the second workbook.

Type a comma.

Using the mouse, navigate back to the first workbook and select the range of customer numbers to be deleted. Because this is in a separate workbook, Excel should automatically add the dollar signs throughout the cell reference. This is good; you want the dollar signs to be there.

Type a comma, a zero, and a closing parenthesis. Press Enter (see Figure 1).

Fig. 1

MrExcel 2-3-11 Fig01

 

One of two things will happen. You might get a number as the answer to the MATCH query or you might get an #N/A error (see Figure 2). Normally errors are bad, but in this case, the #N/A error is telling you something very important.

Copy your formula down to all of the records in the second workbook. Any results that have a number should be deleted. The number indicates that this customer is found in the first workbook. Any results with an #N/A should be kept. The #N/A error indicates that the customer number is not found in the list of records to delete.

Fig. 2

MrExcel 2-3-11 Fig02

 

Sort the second workbook by the Match column descending. This should put all of the #N/A records at the top of the list. Keep those records. Any records with a number at the bottom of the list should be deleted.

You can now delete the temporary Match column in the second list (see Figure 3).

Fig. 3

MrExcel 2-3-11 Fig03

 

Bill Jelen is the author of 32 books about Excel. His next Webinar for CFO will cover Excel for budgeting and planning and include “What-If Scenarios” and “Monte Carlo Analysis.” You can catch the Webcast live on Tuesday, February 22, 2011. In addition, send a question to Bill using the CFO Excel Community Center (right) for a chance to win a copy of one of his books. If your question is selected as a topic for his column, we’ll send you one of his most popular titles.

4 Powerful Communication Strategies for Your Next Board Meeting