I am using Microsoft Excel 2013. I am hoping for an explanation on how to accomplish the following using Excel...
I have a list of approx. 1000 9-character sequences of letters (ex: GANTQYFGA) in one column (column B).
In the next 9 columns (column C-K), I have each letter from the respective sequence in its own separate cell (ex: c2-G, d2-A, e2-N, etc)
I want to assign a number value (essentially a score) to each letter in the sequence, depending on its position in the sequence. For example, if a "G" is in position 1, it's score would be X. If a "G" is in position 8, it's score would be Y. There are 20 possible letters that can be found in 9 possible positions, for a total of 180 individual score values that are in my "key". To be brief, the values in my "key" are from previous data. For my new data set, I am attempting to score each 9-letter sequence based on values calculated from my previous data (values are based on the frequency at which a certain letter was found at that position in the sequence).
Rather than go through each 9-letter sequence, refer to my key, and manually type in each value (for roughly 1000 sequences, this would require me to manually enter 9000 values!), I am hoping there is a way to input my "key" into excel and have it fill in the values for each position of every sequence.
Can anyone help me figure out how to do this? I have a very basic skill level using Excel. I've never used any advanced programming or macros.
I have attached a copy of my .xls data file for reference. On the right ride (columns AA-AJ) is how I would like the output to look. Ignore columns A, L & M, as well as P1-3 & Q1-3 and any highlighting (irrelevant to this question).
I would really appreciate any assistance!
Thanks,
Alex
Bookmarks