Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Greetings

I have an some survey responses with values like "VU" for "Very Unlikely" and "S" for Sometimes. Each survey response occupies a cell. For each cell containing a survey response, I want to fill another cell with a corresponding number. For example, for every cell containing "VU" I want to fill a corresponding cell with the number 1.

How is this done?

share|improve this question
add comment

3 Answers

up vote -1 down vote accepted

I wound up settling on a good old-fashioned array look-up like this:

=LOOKUP(J4, {"A","D","NS","O","R","S","SA","SD","VO","VR";4,2,3,4,2,3,5,1,5,1})

However, in some cells, the cell reference that is the LOOKUP's first argument pointed to an empty cell resulting in an "N/A" error. My response to this was to apply the NOT() and ISBLANK() functions like so:

=IF(NOT(ISBLANK(J4)),LOOKUP(Sheet1!J4, {"A","D","NS","O","R","S","SA","SD","VO","VR";4,2,3,4,2,3,5,1,5,1}),"")
share|improve this answer
    
I think you may have over-complicated this solution. –  Sux2Lose Apr 30 '10 at 15:56
    
Nothing personal. I don't think it's the best answer. –  Sux2Lose May 7 '10 at 16:23
add comment

I would have a seperate table in another tab that details the values:

Code   Value
VU     1
U      2
S      3
L      4
VL     5

Then, in a new column, on the survey I would add this formula:

=VLOOKUP(A2,CodeSheet!$A$2:$B$6,2,0)

where A2 refers to the respondents answer for that row and Codesheet refers to the sheet where you put the above value table.

Now you should be able to sort by the new column.

share|improve this answer
    
I tried this approach and it kept failing because the codes aren't in alphabetical order. –  Keyslinger Apr 29 '10 at 17:58
2  
Did you have the last argument in the formula? the ',0)' without the quotes? The order doesn't matter if you do. –  Sux2Lose Apr 30 '10 at 15:51
add comment

You can easily do this with a formula. Something like:

=IF(A1="VU",1,IF(A1="S",2,IF(A1="L",3,IF(A1="VL",4,""))))

Where A1 is the cell containing the response.

I used Very Unlikely, Sometimes, Likely, Very Likely as possible responses.

It would also be possible to create a VBA macro to do this, but i think that would be complicating it...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.