How to Take a Shortcut for Making ABI 3100 Plate Data (Smaple Sheet)

It has become a bottelneck that preparation of a plate data for operating ABI3100 takes time, because the machine quickly outputs a big amount of data. It is especially the case using POP7 on 50cm caps with a rapid run module. Software aided preparation of plate data could reduce the operating time. I present here a semi-automated procedure to prepare ABI 3100 plate data with EXCEL.

Format of the ABI3100 Plate Data
Export a plate data to your My Document folder. A TAB-delimited text file appears.



Data circled with green are fixed according to users and instrument/analysis protocols. Data circled with orange are individually changed according to the plate ID (the file name without suffix) and sample names.

Use of EXCEL for Preparation of Plate Data
The simplest way for preparation of the plate data is to open this exported file with EXCEL, edit and save the file as a TAB-delimited text as a new name. The EXCEL spreadsheet is more convenient than UDC spreadsheet. The new file can be imported by the UDC plate manager. Be careful so that the new file has CRLF line saparators without any garbage strings such as excess lines, TABs, spaces, etc. Check it out with a text editor.
It would be more convenient if the sample sheets for PCR and sequencing reactions are also made with EXCEL. You can paste the sample data from these sample sheets to the ABI 3100 plate data. For example, make a sequencing sample sheet as shown below.



Sample data are pasted from a PCR sample sheet of similar format.
In this sheet, samples are arranged horizontally. This arrangement is useful to avoid mis-handling of samples and chemicals. On the other hand, samples in the UDC spreadsheet are arranged vertically. How to convert these arrangements? Use the transpose function shown by icon. Drop down this icon and choose the function to paste. If cells at origin contains some particular format or function data, choose paste special, check value and transpose, and paste.



Now we have converted the columns and rows, but the columns are inverted as H01..A01. Then sort whole data on column A as a key to invert the data rows.



It looks like what we wish to have. Next, stack the data vertically by copy & paste function.



Use "&" symbol to make data in a number of cells together. In this case you might make a sample name such as "herring-8-M13-21" (sequencing herring #8 clone with the M13-21 primer). Input [=B1&"-"&C1&"-"&E1] in F1 cell and drop it down, then you can get you want in column F. Paste these sample names onto the plate data template. Save it as a TAB-delimited text.



EXCEL Files for Automation
For EXCEL spreadsheets to do above automatically, click here. Unzipping you may find three files; PCR sample sheet, sequencing sample sheet and book to make ABI3100 plate data. Simplest instructions are written in the spreadsheets.

Finally after you have saved the plate data as text, check garbage strings (excess lines, TABs, spaces, etc) carefully to delete. Import the text file into UDC to run.

---