• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel data report automation


New Member

Sorry for bothering you with such a simple request, but I need help with the best approach to the following:

I have several columns of information (customer survey data) that I need converted from text (excellent, good, fair, etc.) to numbers. Each month I want to add more survey data (text) to the spreadsheet and have the results automatically calculated.

I know I need to place the score matrix in sheet 2, but could you please help me with the function that would most efficiently convert the text into numbers?

Thank you for your time.

If you structure your score matrix as below and then use the vlookup function this will pull through the values against your text:

Col A ColB

Excellent 4

Good 3

Fair 2

Poor 1


eg: vlookup("Excellent",$A$1:$B$4,2,false)

I hope this is what you were after.