PDSWSS
asked on
I need to transform the Excel spreadsheet described below. What code would I use?
This question is a variation on a couple of similar questions I posted recently.
I have attached an Excel 2007 spreadsheet (Sample) that contains a small sample of a larger output file.
Another sheet is included with the expected output.
These data represent 4 subjects (Column A) who answered 7 questions. Questions 1,2, & 7 have 5 possible answers numbered 5,4,3,2,1 represented in columns B:K (Q 1-2), AB:AF (Q7) in Row 1. Questions 3 - 6 have 4 possible answers numbered 1 - 4 (Q3,4,5) and 4,3,2,1 (Q6) in columns L:AA in row 1. PLEASE NOTE: Questions 1 and 3 have text included in the answer in addition to the number in Row 1.
Each subject chose 1 of the 5 answers for Q 1-2, 7 and chose one of the 4 answers for Q 3-6. The answer they chose is marked in that subject's row under the appropriate column with a 1. I need code that would transform each answer into the actual numerical choice shown in the same column in Row 1.
This number should be displayed in the appropriate Subject's Row under the column names for each question -Q1 – Q7 (See Output Sheet).
EEReverse.xlsx
I have attached an Excel 2007 spreadsheet (Sample) that contains a small sample of a larger output file.
Another sheet is included with the expected output.
These data represent 4 subjects (Column A) who answered 7 questions. Questions 1,2, & 7 have 5 possible answers numbered 5,4,3,2,1 represented in columns B:K (Q 1-2), AB:AF (Q7) in Row 1. Questions 3 - 6 have 4 possible answers numbered 1 - 4 (Q3,4,5) and 4,3,2,1 (Q6) in columns L:AA in row 1. PLEASE NOTE: Questions 1 and 3 have text included in the answer in addition to the number in Row 1.
Each subject chose 1 of the 5 answers for Q 1-2, 7 and chose one of the 4 answers for Q 3-6. The answer they chose is marked in that subject's row under the appropriate column with a 1. I need code that would transform each answer into the actual numerical choice shown in the same column in Row 1.
This number should be displayed in the appropriate Subject's Row under the column names for each question -Q1 – Q7 (See Output Sheet).
EEReverse.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant answers and thanks again.
A+++
A+++
ASKER
The subject IDs did not appear in column A. But this is trivial. No need to spend any more time.
Have a great weekend.