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: