Generating a numerical value from a set of letters - comparing two lists?

[TheDwarfClunk]

Hi everyone,

I've got a column of groups of letters, (in this case exam results) and I would like to generate a numerical value off that, so that I can get some statistics. So I have a column, which has, say, AAA, ABB, CDU, BEC, A*AAA, BD, U... and next to that I would like an index to show how well overall that candidate did.

So:

Is there an easy way of separating a single cell with multiple letters into multiple cells with single letters (so I won't have to produce such a large look-up table)

How do I cycle through my results, compare the result to the value on the look-up table, and print an adjacent column if they are the same.

Thanks in advance for any help, also, if there's an easier way of solving the problem than my outline above, please tell me!

[Sindhus]

Can you upload a sample file?

[dogberry]

Hi

I think the easiest way for you to achieve this is to use your text to columns to split it up, unfortunatley I dont think it will cope with A* very well so you may have to correct them manually afterwards. Once you have achieved that if you would like to post a sample worksheet of what you have done and what you are after Im sure someone will help


Chris

[TheDwarfClunk]

Hi, that text to columns button was useful, I didn't know about that, thanks.

I'm attaching a file that looks like the one I'll bu using, now, I want to assign a numerical value to each grade, say, A* = 20, A = 15, B = 12, C = 10, D = 8 E = 5, U = 0, then I want to sum the total score in the next empty column. How should I do that, a lot of nested if loops?

[tigeravatar]

TheDwarfClunk,

Welcome to the forum!
Attached is a modified version of your posted workbook.
Note that there is no need to use Text-To-Columns. The formula can handle it without splitting them into multiple cells.
Also, because "A" can be mistakenly found when an "A*" is present, the formula converts "A*" to "S" for calculation purposes.

Row 1 is a header row, so actual data starts on row 2
In column D is the letter to look up
In column E is the corresponding value of that letter

In cell B2 and copied down is this formula to get the total value of the strings that are listed in column A:

=IF(A2="","",SUMPRODUCT((LEN(SUBSTITUTE(A2,"A*","S"))-LEN(SUBSTITUTE(SUBSTITUTE(A2,"A*","S"),$D$2:$D$8,"")))*$E$2:$E$8))