First of all, you need to open your TaqMan exported data. Be sure to remove any superfluous columns. I like to leave the well, sample name, raw data (2 columns), and genotypes intact. This would make your sample name be in column B, and the genotype data be in column E. Next, change the genotype data to be in the same format that you use in your spreadsheets. I do this quickly and easily using the Find/Replace function in the Edit menu of Excel. Then, change the sample names to those found in your spreadsheet. A simple way to do this is to make up a template sheet, where each PCR plate that you use has its samples listed. This way, you just need to cut and paste into your exported data. You should be ready to run the macro now!
This is a good example of a macro that I create a link to on my Excel toolbar. For more on this, see this page.
When the macro starts, you will be asked what is the current sheet. Choose the sheet that holds your exported data from the box. If it isn't there, that is because it isn't open! Then, you will see the same sort of form asking for your database. Choose the workbook that holds your compiled data. Again, if it isn't there, it isn't open. Now you will see another form with the names of your polymorphisms. These are taken from your workbook that holds your compiled data. This needs to be in my normal format, with the first row giving the name of the study, and the names of the polymorphisms. The second row has the alleles present for each polymorphism. Choose the polymorphism, and off you go!
I have also used this macro to update the data in my spreadsheets with respect to data other than genotypes. Often, the clinicians in our studies send us data about the subjects. You can modify the macro to work with this sort of data as well. If your compiled data is different than my standard format (for example if you use it in STATA, with only one row of descriptive information), the macro will still work. However, if the format of the data to be input is different than TaqMan exported data, you only need to change two things. Open the Visual Basic editor while you have the taqmanin.xls workbook open. In the Add_Data4 module, you will need to change three lines:
The line "y1 = 4" replace the 4 to be 1 less than the row where your data starts.
The line "sample = Cells(y1, 2)" replace the 2 to be the column where the ID of the sample is.
The line "genotype = Cells(y1, 5)" replace the 5 to be the column where the data you wish to add is.
That's it!
One other note. You can still store your data in different sheets within the same workbook. I like to keep different populations separate. The macro is written such that if there is data in more than one worksheet, it will check each one in the book. In this regard, be careful that you don't have two individuals with the same ID on different pages. Only the first one will receive data!
Description of the macro.
Download the macro.
Back to the program list.
This page was last updated on: August 28, 2002 by David G. Cox