Tag Archives: excel

Web Development

Align two columns in Excel

I recently had two sets of data, one a full list of records and the other a list of identifiers for the records that needed to be extracted. Extracting the relevant records from the full list would be a fairly easy programming task, but the data was in Excel and I wanted to try and accomplish the task in that environment. Thankfully this problem has already been solved and the answer posted to the web.

If I have one column of identifiers for the records that need to be extracted (column A, 100 records) and one column with the full list of identifiers (column B, 1000 records) the following formula will indicate which identifiers from column B match an identifier from column A:

=IF(ISNA(MATCH(B1,$A$1:$A$100,0)),"","X")

Just place the formula in its own column and copy down for the length of the full data set. The columns do not need to be in any particular order and you can create a separate worksheet that contains your filter list, keeping your data and your filter separate. You can then AutoFilter on your search column to see the results (and copy/paste to another worksheet if necessary).

Web Development

Excel drops empty columns on CSV export

I’m often tasked with getting data from a flat format (Excel, CSV, etc.) into a database. When the format is Excel  and I have only a few files to work with I find it easiest to export the Excel file to CSV or TSV format. This makes it much easier to script the data conversion using PHP. There’s only one problem, Excel doesn’t always represent the total number of columns in each row. If there are blank values in the columns at the end of a row in the spreadsheet then the exported data may have fewer value delimeters than expected.

This bug has been documented by Microsoft for Excel 2003 and earlier. The solution given by Microsoft is poorly worded, but basically it says to make sure the cells at the end of a row always contain data. Not always a realistic proposition. Luckily I found a step-by-step solution that works perfectly:

Put a formula that evaluates to empty (=”” ) in the last column of  the rows that are empty:

  1. select the range in the last column: edit->goto, click on the “Special…” button, Choose “Blanks” and click OK.
  2. type the formula: =””
  3. hit ctrl-enter

Now when you save the document the appropriate number of delimeters should be present, even in rows with empty cells.