Simpler way to pull a cell value from a group based on 2 factors?

[ker_01]

I have a spreadsheet of survey results. The data was pulled from elsewhere,
and so I have a column for each potential answer. For example:
Question 1: I enjoyed my last trip to Europe Yes No NA
would show up as three different columns:
Q1_Yes, Q1_No, and Q1_NA, Q2_Yes, Q2_No, etc. and each has a value of zero
(false) or 1 (true).
[in case you are wondering, the original survey primarily used checkboxes]

Some questions (columns) are not yes/no/NA columns, and may instead have a
text or date answer.

I need to do two things:
(1) determine whether I have more than one "true" per question group, and
(2) return the real value of the correct answer; Example:

Q1: Total true = 1
Q1: Answer: Yes

Ideally, I'd like to calculate both in one formula;
If(totaltrue>1,"MULT",Answer)

Right now I'm working with a long and complicated formula that uses
sumproduct to count totaltrue. If there is a shorter, faster, or easier way
to do this, please let me know.

IF(SUMPRODUCT((LEFT('Raw report'!A1:IU1,4)="B02_")*1,IF(ISNUMBER('Raw
report'!A2:IU2),('Raw report'!A2:IU2),0)*1)>1,"MULT","test")

Then I need to replace "test" with something that would actually return the
column header of the question in that group that has the true value.

So far, my syntax isn't working, and I'm thinking there has to be a simpler
way. Alternatively, I'll just move to VBA and do it there.

Thanks for any ideas!
Keith

 

[smartin]

I have a spreadsheet of survey results. The data was pulled from elsewhere,
and so I have a column for each potential answer. For example:
Question 1: I enjoyed my last trip to Europe Yes No NA
would show up as three different columns:
Q1_Yes, Q1_No, and Q1_NA, Q2_Yes, Q2_No, etc. and each has a value of zero
(false) or 1 (true).
[in case you are wondering, the original survey primarily used checkboxes]

Some questions (columns) are not yes/no/NA columns, and may instead have a
text or date answer.

I need to do two things:
(1) determine whether I have more than one "true" per question group, and
(2) return the real value of the correct answer; Example:

Q1: Total true = 1
Q1: Answer: Yes

Ideally, I'd like to calculate both in one formula;
If(totaltrue>1,"MULT",Answer)

Right now I'm working with a long and complicated formula that uses
sumproduct to count totaltrue. If there is a shorter, faster, or easier way
to do this, please let me know.

IF(SUMPRODUCT((LEFT('Raw report'!A1:IU1,4)="B02_")*1,IF(ISNUMBER('Raw
report'!A2:IU2),('Raw report'!A2:IU2),0)*1)>1,"MULT","test")

Then I need to replace "test" with something that would actually return the
column header of the question in that group that has the true value.

So far, my syntax isn't working, and I'm thinking there has to be a simpler
way. Alternatively, I'll just move to VBA and do it there.

Thanks for any ideas!
Keith

I think your formula could work, but the task will be somewhat simpler
if you dissect the question header into constituent question number and
question answer/value information in helper cells (rows). I did it like
this, see if it helps:

In the Raw report sheet insert two new rows below row 1.

Row 1 is the original data:
B01_Yes B01_No B01_NA B02_Yes B02_No B02_NA ...

Row 2 has formula
=LEFT(A1,3)
(Result: B01 B01 B01 B02 B02 B02 ...)

Row 3 has formula
=MID(A1,FIND("_",A1)+1,99)
(Result: Yes No NA Yes No NA ...)

Rows 4- have survey data.

Then in separate sheet column A I placed
B01
B02
B03
B04

and in B1, this *array* formula:
=IF(SUMIF('Raw report'!$A$2:$L$2,A1,'Raw
report'!$A$4:$L$4)>1,"MULT",INDEX('Raw
report'!$A$3:$L$3,1,MATCH(1,(A1='Raw report'!$A$2:$L$2)*('Raw
report'!$A$4:$L$4),0)))

(sorry about the word wrap)

I omitted some error-checking (IF(ISNUMBER... ) that may be important.