__Problem__: Someone gives you a file containing a partial description of what's in a 384-well plate. Yet you need to place that within the context of a full 384-well plate description. (the more general problem is how to graft small lists onto larger lists when the lists have a common matching element)

For example, the list below is missing even-numbered wells, and some rows (plate row B). You might have to convert the list below such that the second column has all 384-well plate positions: A01, A02, A03, etc through P24.

Plate Well Name Gene Len Tm Chr 18 A01 YAL034C-B 70 72.4 I 18 A03 YAL042C-A KRE23 70 72.4 I 18 A05 YAL044W-A 70 76.5 I 18 A07 YAL056C-A KRE20 70 76.5 I 18 A09 YBL029C-A 70 75.9 II 18 A11 YBL071W-A KTI11 70 74.2 II 18 A13 YBL108C-A 70 77.7 II 18 A15 YBR058C-A TSC3 70 74.7 II 18 A17 YBR085C-A 70 74.2 II 18 A19 YBR103C-A 70 73.6 II 18 A21 YBR233W-A DAD3 70 74.2 II 18 A23 YBR255C-A 70 77.7 II 18 C01 YCL001W-B 70 71.8 III 18 C03 YCL021W-A 70 76.5 III 18 C05 YCL027C-A 70 74.2 III 18 C07 YCL057C-A 70 76.5 III 18 C09 YCR038W-A 70 75.3 III 18 C11 YCR097W-A 70 69.5 III 18 C13 YDL085C-A 70 74.7 IV 18 C15 YDL114W-A 70 70.6 IV 18 C17 YDL159W-A 70 71.8 IV 18 C19 YDL185C-A 70 71.8 IV 18 C21 YDL240C-A 70 73 IV 18 C23 YDL247W-A 70 70.1 IVRather than go through manually and insert all the required rows, you can use excel and the VLOOKUP function to graft your list onto a full 384-well plate list. The idea is create an index for the incomplete list, and then use vlookup to compare the two 384 well lists and copy the index to the complete list when a match is found. Once the index is in place across the full 384 well list, the file can be sorted by index, which puts the matching part of the full 384 well list in the same order as the partial 384 well list. The two lists can then be combined to the same document, then sorted by the full 384 well list column. This action has the side effect of decompressing the partial list across the full list, thus providing blank lines in all the appropriate places. This can be accomplished as follows:

- open the file with excel (file 1), sort by Well.
- insert two blank columns on left edge.
- copy the well locations to the first column to use as an index for vlookup
- in the second column make an ascending integer list
- open a new worksheet (file 2) and create a full 384 well plate list (A01-P24, 384 entries), sort ascending.
- in the second column insert the
**VLOOKUP**function with the following criteria: =VLOOKUP(A1, 1st two columns of file 1, 2, FALSE)- The first argument to vlookup is the value to look up in a table array specified by the second argument.
- The second argument is a table of consisting the first two columns of file 1, also referred to as a table array. The first column of this table will be searched for the specified value.
- The third argument to vlookup is the column of the table array from which a value should be returned, if a match is found by the lookup). In this case you want to return the index value you made - which should be in the second column.
- The fourth argument (optional) specifies whether an approximate match should be returned incase an exact match can't be found.

- use
**fill down**to copy the formula to each cell of the second column. - When this is done, some of the cells in column 2 will have a match and others will not.
- sort the sheet by the index. This sheet with the full 384 well plate list, now has a portion which matches the order of the partial 384 well list.
- Go back to the partial list (file 1) and copy everything but the first two columns
- paste it along the matching edge of the full list.
- Now you can get rid of any extraneous columns, and when you sort the list by the column containing the full 384 well plate designation, the "partial list" portion will be expanded/sorted into a full 384 well format.