Category Archives: Software

There have been a number of interesting projects I’ve been asked to work on over the course of the last few weeks. They all involve writing a little code and so I want to document some of those projects here. They are not math related but my interests are broader than just numerical analysis and bioinformatics. Plus, in the age where “google” has become a verb, I attract occasional visitors to this site looking for solutions to problems that I have solved for myself, like repairing a broken headphone jack or enabling wifi on a misbehaving smart phone.

Consolidating data in multiple excel workbooks

Consider the case where you might have a large set of excel spreadsheets spread across a number of different workbooks. If you want to consolidate, merge, extract or just combine data, there isn’t really an ideal tool for doing this. There is a “Consolidate data” function in excel but it isn’t suited to large numbers of files because it becomes tedious opening and selecting the range over and over. While many Excel gurus might have other solutions, I decided to go the macro route.

As in most cases, I was able to find resources available online that would accomplish something similar to what I wanted.

Goal #1:

Take all the data (used range of cells) in all the worksheets in all the workbooks in a specified directory and combine them into a single sheet (or several sheets in the same workbook if they don’t fit)

Like this:

In my Fall course of Math Models, I have three groups working on projects to finish up the semester. One of the groups have an assignment to explore a model of the spread of a forest fire. The assumptions are that the trees are on a rectangular grid, or a lattice. The time is a discrete variable and at each time step the probability that the fire spreads from one point in the lattice to an adjacent point (up, down, left or right) is given by p. For simplicity, the event that the fire spreads to each point is assumed to be independent of any other point.

Part of their project is to implement a numerical simulation of their forest fire. I couldn’t let them have all the fun, so below is an example of my version of the simulation in MATLAB. I have to hold off on posting the code until after they have handed in their project.

In the graphical representation of my simulation, green represents an unburnt tree, black is burnt and red is currently on fire. The fire lasts for exactly one time step. I also implemented a 3-D version, where a height of 1 is unburnt, 2 is on fire, and 0 is burnt. I’ll confess to having way too much fun with this.

I have used a 200×200 lattice with p = 0.5.

Update (3/4/16)

I promised a LONG time ago that I would post the code. I finally got around to it. Here are links to the m files that were used to generate the graphical simulations above:

Like this:

I had to solve two problems for myself today. I am posting my solutions here, mainly for my own reference but maybe somebody out there might have the same issues to be solved.

The first problem I faced was installing a network printer so that it would be available to all users on that machine. This is probably a minor problem for seasoned IT pros, but since I am not one, it took some investigating. I learned that local printers are installed automatically for all users, while network printers are associated with user profiles. This means that when you install a network printer it is only available to the user profile that you used when installing.

The solution is to install the network printer as a local printer. In other words, go to Control Panel .. Printers. Click "Add a printer". Select that you want to install a local printer. At this point you will create a new port, using a Standard TCP/IP port. You’ll need to have the IP address of the printer to do this and you’ll also want to have the drivers handy.

Since it is installed as a local printer it will now be available to all users when they log in. The bug I still haven’t worked out, though some of you may have an idea, is that even though I have selected it to be the default printer in my profile, it is not necessarily the default printer for other users. If is the first printer installed, no problem, but otherwise it is not the default for other users.

Like this:

Thanks to a post in LifeHacker, I have set up a few bookmarks in Firefox that will open in the sidebar. I recently discovered the trick of opening links in the sidebar but I did not think to use mobile apps such as the new iphone app in Google Reader.

Create a new Virtual Machine, by clicking Machine .. New (or just New on the toolbar). Then click “Next >”

Give your Virtual Machine a name and then select the operation system you intend to install. Then click Next…

Now, select the amount of memory (RAM) to dedicate to this machine when it is running. You don’t want to use all of your available memory since your system will still be running. I have 2 GB on my laptop so I dedicate anywhere from 512 – 1024 MB of RAM. Then, click next.

Next, VirtualBox will create a virtual hard disk so you can set up how much hard disk space your virtual machine will have available. Click New to set up a new virtual hard disk, go through the steps to set one up. I used the settings below:
Dynamically Expanding Image.
20 GB
Click Next.

Now you can click finish to finalize the setting up of the Virtual Machine. Click Finish.

Installing Linux

In VirtualBox, right-click on the machine you just created and select Settings. Click on CD/DVD-ROM in left hand window of the Setting dialogue box. Select Mount CD/DVD Drive and click ISO Image File. Browse for the ISE image you downloaded. Then click OK.

Now, click Start and go through the Setup procedure for the Linux distribution.

Like this:

Both Microsoft Access and Microsoft Excel became heavily used tools in my bioinformatics research. Scripting came in handy on occasion to automate tasks but my lask of “skills” in that arena left me relying heavily these applications. Within the last two months, I couldn’t count how many tables and queries I’ve created (point-and-click style) in Access, how many times I used VLOOKUP and MATCH in Excel. I’ve just recently discovered the many, many joys of Pivot Tables (amazing!). Oh and by the way, I’m flat in love with Excel 2007.

Here were a couple of new tips I stumbled across today:

Convert one column into twoSuppose you have a list of full names in a column, and you’d like to separate the first names from the last names. In Excel 2003, select the column and click Data*Text to Columns. In Excel 2007, click the Data tab, and select the Text to Columns button. In both versions, choose Delimited (unless all the entries are the same length, in which case you can select Fixed width), click Next, and check Space (or whichever option applies; see the screen below). You can leave “Treat consecutive delimiters as one” checked. Click Next again to view data-formatting options, and then Finish.

Select Space (or whatever separates the items in the column you wish to split) to create two columns where there once was one.

At this point, you may want to change the order of the columns. To do so, simply select the column header, right-click the selection, and choose Cut. Now click the header of the blank column you want to place the cut cells in, right-click, and select Insert Cut Cells.

Paste formatting with one keystrokeIf you’d like several disconnected cells to share a format, such as bold text and a background color, it can be a hassle to select each cell one at a time, open its cell-format dialog box, and make the changes you want. Instead, reformat one of the cells, and then select all of the others by pressing Ctrl, and clicking them one by one. Once they’re all highlighted, press F4 to apply the formatting to all of them at once.

Like this:

I had a few major issues but was finally able to find the correct solution to my problem.

Problems:

1. After setting up separate partition with Partition Magic and then install Fedora Core 8, the Windows system would not boot saying “autochk autocheck not found” and going straight to the dreaded blue screen of death. I discovered that Partition Magic (or the Fedora install) marked the NTFS partition as hidden. I had to use a utility called BootIT NG, creating a bootable CD and using this utility (without installing to) to switch from a hidden NTFS partition to not hidden.
Problem 1 Solved.

2. This one took a lot longer. I have Dell Latitude D420 with a Broadcom 1390 WLAN MiniCard. These drivers were never offered for Linux so the necessary drivers had to be reverse engineered (not by me, of course, but they are out there). There are apparently two options which as a Linux-noob, I couldn’t tell more than that. I tried a number of forums and went step by step through the provided procedure. I did at least 3 fresh installs of the entire operating system, after I felt I had mucked up the system beyond fixing. Finally, the one the worked was here:

I’m going to quote most of it here for future reference, but I give them full credit for something that works. And definitely use the NetworkManager hint below. I used the b43 drivers below and everything worked.