PDSWSS
asked on
How would I make the transformations described in this question using code?
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 8 questions. Questions 1,2,3, 7, 8 have 5 possible answers numbered 0-4, represented in columns B:P ( Q 1-3) and Z:AI (Q 7,8) in Row 1. Questions 4,5,6 have 3 possible answers numbered 0-2 in columns R:Y in row 1. Each subject chose 1 of the 5 answers for Q 1-3, 7,8 and chose one of the 3 answers for Q 4-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 – Q8(See Output Sheet).
Thanks
EE3.4.xlsx
Another sheet is included with the expected output.
These data represent 4 subjects (Column A) who answered 8 questions. Questions 1,2,3, 7, 8 have 5 possible answers numbered 0-4, represented in columns B:P ( Q 1-3) and Z:AI (Q 7,8) in Row 1. Questions 4,5,6 have 3 possible answers numbered 0-2 in columns R:Y in row 1. Each subject chose 1 of the 5 answers for Q 1-3, 7,8 and chose one of the 3 answers for Q 4-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 – Q8(See Output Sheet).
Thanks
EE3.4.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. If you mean instead of 1 the cells contain e.g. A1 then yes either your sheet or the code would have to be changed.
2. Not sure I follow, do you mean row 1, A is a column?
2. Not sure I follow, do you mean row 1, A is a column?
ASKER
I sent the message above to StephenJR before I got kgerb's solution.
Did not test kgerb's solution yet.
Did not test kgerb's solution yet.
Btw Kyle has also posted a solution so please acknowledge that.
Cross-posted.
ASKER
StephenJR
1. If you mean instead of 1 the cells contain e.g. A1 then yes either your sheet or the code would have to be changed.
More complex - eg 1. No increase, 2. feels talkative, 3. fairly tired, 4. very tired. I would need code that would delete the text.
I will make a separate post.
2. Not sure I follow, do you mean row 1, A is a column?
My mistake Row 1 not Row A. Sorry about that.
Q. CORRECTED - Also would your code work in the case where the answers in Row 1 went in ascending and then descending order eg 123455432112344321
1. If you mean instead of 1 the cells contain e.g. A1 then yes either your sheet or the code would have to be changed.
More complex - eg 1. No increase, 2. feels talkative, 3. fairly tired, 4. very tired. I would need code that would delete the text.
I will make a separate post.
2. Not sure I follow, do you mean row 1, A is a column?
My mistake Row 1 not Row A. Sorry about that.
Q. CORRECTED - Also would your code work in the case where the answers in Row 1 went in ascending and then descending order eg 123455432112344321
All this could be catered for, I suggest you post an illustrative workbook and explain which bits might vary and how.
ASKER
kgerb
Tried your code and got Runtime error '9'
Subscript out of range p
On Debug Highlighted: "With Sheets("OutputExample (2)").Cells(j, 1)"
Any ideas?
Tried your code and got Runtime error '9'
Subscript out of range p
On Debug Highlighted: "With Sheets("OutputExample (2)").Cells(j, 1)"
Any ideas?
ASKER
StephenJR
Thanks. Will post as a separate question and will wait to award points until I hear back from Kyle.
Thanks. Will post as a separate question and will wait to award points until I hear back from Kyle.
Just a syntax error in Kyle's code, change to
With Sheets("OutputExample").Ce
With Sheets("OutputExample").Ce
Stephenjr,
Thank you for correcting my code. I created another sheet in my example workbook so I would not blow away the original values. I forgot to change the code back. Thanks again.
Kyle
Thank you for correcting my code. I created another sheet in my example workbook so I would not blow away the original values. I forgot to change the code back. Thanks again.
Kyle
ASKER
That's very generous. First time I have seen one expert solve a bug in another expert's code.
In this case, should I split the points?
In this case, should I split the points?
That's fine with me, thanks.
ASKER
Thanks for both of your solutions.
ASKER
I just posted a variation of this question. Thanks
https://www.experts-exchange.com/questions/27409699/I-need-to-transform-the-Excel-spreadsheet-described-below-What-code-would-I-use.html
https://www.experts-exchange.com/questions/27409699/I-need-to-transform-the-Excel-spreadsheet-described-below-What-code-would-I-use.html
ASKER
You are good. One try worked perfectly.
Q. I have two more forms I need to transform. However, they have text in addition to a number in Row A in each column.
Would I need to delete the text for your code to work in these cases?
Q. Also would your code work in the case where the answers in Row A went in ascending and then descending order eg 12345543211234554321
Thanks