Add a Customer Number to Each Detail Record

November 04, 2022 - by Bill Jelen

Problem: I’ve imported a data set where the customer information appears once in column A, followed by any number of invoice detail records. At the end of the first customer, the next customer number is in column A and then there are detail records for that customer. You can not sort this data. The customer information needs to be in its own columns on each record.

Another annoying data set. There are four rows in A3:G6 which belong to Air Canada. Rather than put Air Canada in each row, you have a single cell in A2 with "Acct A4651 Air Canada". Another customer heading in A7 says Acct A8736 Ainsworth, followed by all of the records for Ainsworth. — Figure 803. Another annoying report format.

Strategy: This is a common data format, but it is horrible in Excel. Here’s how you fix the problem:

1. Insert new columns A and B. Add the headings Acct and Customer. Here is the basic logic of what you want to do: Look at the first four characters of column C. If they are equal to Acct, then you know this row has customer information, so you take data from that cell and move it to column A. If the first four characters are anything other than Acct, you use the same account information from the previous row’s column A.
2. Enter the following formula into cell A2: =IF(LEFT(C2,4)=”Acct”,MID(C2,6,5),A1). Copy this formula down through column A. you copy this formula down, it does the job. In cell A2, the IF condition is true and data is extracted from C2. In cell A3, the condition is not true, so the value from A2 is used. In cell A7, a new customer number is found, so the data from C7 is used in A7. Cells A8 through A59 get the customer number from A7.

Insert two new columns for Account and Customer. The formula described at left extracts the correct account number over to column A. — Figure 804. Use IF to extract and copy account number information.

Similar logic is needed in column B. In this case, though, you need to grab the customer name. You know that the word Acct and the space that follows it take up 5 characters. You know that your account number is another 5 characters, and then there is a space before the customer name. You therefore want to ignore the first 11 characters of cell C2. You can use the formula =MID(C2,12,50) to skip the first 11 characters and return the next 50 characters of the customer name. Use this formula as the TRUE portion of the IF function.

3. Enter the following formula into cell B2: =IF(LEFT(C2,4)=”Acct”,MID(C2,12,50),B1). Copy this formula down through column B.

A formula in B gets the Customer Name from the heading over to column B. — Figure 805. Extract customer information.

You have now successfully filled in the account and customer. You need to change these formulas to values.

4. Highlight columns A and B. Press Ctrl+C to copy. Choose Home, Paste dropdown, Paste Values to convert the formulas to values. You do this to remove the customer heading rows. As you think about a method to isolate the heading rows, you will notice that heading rows are the only rows with blank cells in column D. You can move the blanks to the end of a data set by sorting the data by column D.
5. Select the heading in D1. Select Data, AZ to sort ascending by date. Any rows that have no value in column D will automatically sort to the bottom of the data set.

After filling in the Account and customer, convert them to Values. Sort by Column D and all of those extra heading rows will sort to the bottom where they can be deleted. — Figure 806. The extraneous heading rows sort to the bottom.

6. With the cell pointer in D1, press the End key and then the Down Arrow key twice. The cell pointer will be located on the first customer heading. Delete all the rows below row 564.

Results: You have a clean data set with customer information on every row. You can sort this data and otherwise use it for data analysis.

You are left with Account in A, Customer in B, Invoice in C, Date in D, Quantity in E, Product in F. — Figure 807. Customer data has been added to each record.

This article is an excerpt from Power Excel With MrExcel

Title photo by Yizheng Duanmu on Unsplash