One of the more frequent requests I get is how to convert a text file into viable Excel data that can be pivoted? There are a wide variety of text files. I call them textual variants, and there are different methods used to refine the file depending on the variant. The first example is a common one. This is an accounts receivable aging file. The basic problem here is that a header line appears first which identifies the customer name. All of the other pertinent information (date, invoice number, amount, etc.) is contained in subsequent lines. Then there is a break and the cycle repeats itself. This file has to be manipulated so the customer name is on the same line as the rest of the data in order to make a data base which is suitable to pivot. A manual insertion of a column and manual entry of the customer name followed by copying the name down through all of the data rows is one option, but this is not a good option if the file is thousands of rows deep. Usually these files can be easily adapted using a combination of text functions, IF functions and/or the special paste feature in the “Go To” dialog box.
Here is how the file appears when it is imported into Excel (click to enlarge):
Now insert a column before column A and write an IF function. The syntax of an IF function is to first perform a logical test i.e. a function that is answered by true or false. If true, the IF function will perform the next function (part 2 of the IF) and ignore the last function (part 3 of the IF). If false the IF function will ignore the next function (part 2 of the IF) and perform the last function (part 3 of the IF).
Click to enlarge:
When copied using relative references the formula in cell A4 became =IF(ISNUMBER(D4),A3,C4&D4)
It found a number in cell D4 and returned the value in cell A3, namely ALAMO GROUP.
Then the formula in cell A6 did not find a number and returned the values in cells C6 & D6, blanks.
Likewise the formula in cell A7 found a label and returned ALEXANDRIA TUBES, and so on.
The effect is to record the customer name on each row that contains the rest of the data.
First, highlight the IF functions in column A and paste special values so that the formulas are erased and the text remains.
Well the temperature hit zero degrees here yesterday. With the wind chill it seemed like about 150 below, and as my brother in Maine said “It’s wicked cold”, so I’m headed to sunny Florida tomorrow for a brief respite from the bitter weather. I want to wish all my readers a save and happy holiday season. Rest assured, there’s more Excel excitement to come in 2009.