MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Gotta be a better way?

  Asked By: Joel    Date: Oct 15    Category: MS Office    Views: 1535

I'm converting a lot of text survey results into numeric results, and
I have it working, but it seems really cumbersome. Also, my biggest
spreadsheet (about 7000 rows) takes 20 minutes to process, so I think
I'm doing something wrong.

i'm currently using blocks like:

If Cells(Emailrow, 6).Value = "Very Satisfied" Then 'Satisfied
Response Time
Cells(Tallyrow, 4).Value = 5
ElseIf Cells(Emailrow, 6).Value = "Somewhat Satisfied" Then
Cells(Tallyrow, 4).Value = 4
ElseIf Cells(Emailrow, 6).Value = "Neutral" Then
Cells(Tallyrow, 4).Value = 3
ElseIf Cells(Emailrow, 6).Value = "Somewhat Unsatisfied" Then
Cells(Tallyrow, 4).Value = 2
ElseIf Cells(Emailrow, 6).Value = "Very Unsatisfied" Then
Cells(Tallyrow, 4).Value = 1

End If

But this is giving me about 30 or so if/then statements per page.

Is there a way to code in a reference table something like:

Very Satisfied=5
Somewhat Satisfied=4
Neutral=3 etc

And then have it just look cells up on the reference chart as it
reads new rows?

Part of this is complicated by the fact that different questions have
different text answers. For instance Very Satisfied, Strongly agree,
and Completely Solved are all =5 depending on what the question was.

Is having an if/then for every column really the best way for me to
be doing this?



7 Answers Found

Answer #1    Answered By: Maliha Malik     Answered On: Oct 15

I wouldn't even bother using VBA as you were attempting. Just use a
VLOOKUP() function within EXCEL. For example, to hardcode the list,
you could do something like:

=VLOOKUP(cell_to_translate,{"Very Satisfied",5;"Neutral",3;"Strongly
Agree",5;"Agree",4;"Somewhat Satisfied",4},2,FALSE)

Or, if you create the same lookup array in columns A and B, with each
cell in column A having your text  and the associated cell in column B
having its numeric  value, you could simply do:


Answer #2    Answered By: Edward Jones     Answered On: Oct 15

That's nice and clean, but any cells where the source has no data and
up as a "#N/A". I'm averaging some of the results, and the "#N/A"
fields make the averages display "#N/A" as well.

Answer #3    Answered By: Lewis Welch     Answered On: Oct 15

What about wrapping an IF(ISError......) around it? or an
IF(ISBLANK...)... something like that?

Answer #4    Answered By: Mike Stephens     Answered On: Oct 15

U can do one thing. U can define one array variable which will store all ur
required values like very satisfied, satisfied etc. Then u perform whole
operation in loop. Inside that loop if u know where to put rquied values like
satisfied, very satisfied etc. means that if it is fixed then u just write code
inside if ....Else if..... for performing the operation. It will become easy for
u. And also it makes sense for u.

If then also u get less speed then tell me, bcaz i am also working  with database
and worked with more that 2,00,000 rows at a time. And i know that how to work
with it. I have one another method. But then u just explain me ur problem in

I hope that above option work correctly for u.

Answer #5    Answered By: Adalric Fischer     Answered On: Oct 15

You can create a function to convert the response and call it as you
step through the survey. Use Select Case inside the function so you
can assign a given value for more than one answer. Here is a snippet
to demonstrate what I'm talking about.

Cells(Tallyrow, 4).Value = ConvertAnswer(Cells(Emailrow, 6).Value)

Public Function ConvertAnswer(s As String) As Integer
Dim i As Integer

Select Case s
Case "Very Satisfied", "Strongly Agree", "Completely Solved"
i = 5
Case "Somewhat Satisfied", ...
i = 4
Case "Neutral", ...
i = 3
Case "Somewhat Unsatisfied", ...
i = 2
Case "Very Unsatisfied", ...
i = 1
Case Else
'handle error
End Select
ConvertAnswer = i
End Function

Answer #6    Answered By: Julia Flores     Answered On: Oct 15

Just a comment about speed of processing your largest spreadsheet: I
notice that you select sheet "Tally", do something, select sheet
"Email", etc. This does take a lot of time. Have you thought of using
named ranges instead? Since you are working  with different cells in the
sheets, you can refer to a named range with a relative offset, and then
do what you do without ever opening the sheet.

Works like a charm for what I do. I presume you have
application.screenupdating = false and later True in your code as well.
If you do not, this will also speed up things dramatically.

Answer #7    Answered By: Jarvia Miller     Answered On: Oct 15

Use AverageA instead. It will treat the text  values as a 0.

Didn't find what you were looking for? Find more on Gotta be a better way? Or get search suggestion and latest updates.