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 report format.
Strategy: This is a common data format, but it is horrible in Excel. Here's how you fix the problem:
- 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.
- 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.
- 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.
- Enter the following formula into cell B2: =IF(LEFT(C2,4)="Acct",MID(C2,12,50),B1). Copy this formula down through column B.
- Extract customer information.
You have now successfully filled in the account and customer. You need to change these formulas to values.
- 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.
- 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.
- The extraneous heading rows sort to the bottom.
- 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.
- Customer data has been added to each record.