Solved

# What code would I use to make the transformations described in this question below?

Posted on 2011-10-12
Medium Priority
224 Views
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 3 subjects(Column A)  who answered 5 questions, each with 10 possible answers numbered  0-9, represented by the 5 consecutive 0-9 columns in Row 1.
Each subject chose one of the 10 answers for each of the 5 questions. 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, Q2, Q3, Q4, Q5 (See Output Sheet).  Thanks

ISSTestEE.xlsx
0
Question by:PDSWSS
• 7
• 6
• 3

LVL 24

Accepted Solution

StephenJR earned 1000 total points
ID: 36958146
Here is one approach:
``````Sub x()

Dim r As Range, n As Long, i As Long, c As Long

With Sheets("Sample")
.Range("A2", .Range("A" & Rows.Count).End(xlUp)).Copy Sheets("OutputExample").Range("A2")
For n = 2 To .Range("A" & Rows.Count).End(xlUp).Row
Set r = .Cells(n, 2).Resize(, 10)
c = 1
Do While Not IsEmpty(r(1))
i = Application.Match(1, r, 0) - 1
c = c + 1
Sheets("OutputExample").Cells(Rows.Count, c).End(xlUp)(2) = i
Set r = r.Offset(, 10)
Loop
Next n
End With

With Sheets("OutputExample")
With .Range("B2", .Range("B2").End(xlToRight)).Offset(-1)
.Formula = "=""Q"" & column()-1"
.Value = .Value
End With
End With

End Sub
``````
0

LVL 43

Expert Comment

ID: 36958221
You can also use this formula in B2 and copy it down and across

=IFERROR(MATCH(1,OFFSET(SAmple!\$A\$1,MATCH(OutputExample!\$A2,SAmple!\$A:\$A,0)-1,(COLUMN()-2)*10+2,1,10),0),0)
0

Author Comment

ID: 36958279
StephenJR

Thanks. Worked for this example. In the real file there are 15 not 5 questions. How would the code be modified for
15 questions.
0

LVL 24

Expert Comment

ID: 36958302
Have you tried running it? I think it should work for any number.

ssaqibh - very neat, I just can't do formulae.
0

Author Comment

ID: 36958355
Yes. Now it seems to work for more questions and subject. First two times I added more questions got an error.

Thank you very much.
0

Author Comment

ID: 36958529
StephenJR

I am a programming novice.Would you be willing to annotate your code?  Thanks
0

Author Comment

ID: 36958545
ssaqibh
Not clear how your equation would work since it needs to match the output example and in the real scenario the output example would not exist until the code or equation was run.

Thanks
0

LVL 43

Expert Comment

ID: 36958738
Actually you would have to fill row 1 and column 1 before applying the formula.
0

LVL 43

Expert Comment

ID: 36958785
In fact you could use a simpler/shorter formula

=IFERROR(MATCH(1,OFFSET(SAmple!\$A\$1,ROW()-1,(COLUMN()-2)*10+2,1,10),0),0)

You can fill column 1 using the formula

=SAmple!A1

in A1 and copy it down

and fill row 1 with the formula

="Q"&COLUMN()-1

in B1 and copy it across
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 1000 total points
ID: 36958805
Bazooka!!!

Enter this formula in A1 and copy it down and across

=IF(COLUMN()=1,SAmple!A1,IF(ROW()=1,"Q"&COLUMN()-1,IFERROR(MATCH(1,OFFSET(SAmple!\$A\$1,ROW()-1,(COLUMN()-2)*10+2,1,10),0),0)))
0

LVL 43

Expert Comment

ID: 36958811
No need to enter column1 or row 1 seperately
0

Author Comment

ID: 36959091
Entered the formula into A1 and get a circular reference warning. Doesn't appear to work on my end.
See attached screen shot.

Picture-3.png
0

LVL 43

Expert Comment

ID: 36959099
You have to enter the formula on the result sheet not the data sheet.
0

LVL 24

Expert Comment

ID: 36959186
For each row the code looks at the first 10 cells, finds the position of the 1, deducts 1 (ie if 4th cell contains 1 returns 3), puts that in the results sheet, shifts right to the next block of 10 cells and repeats until finds blank cell. Then it goes down to the next row and repeats. The final bit is just putting the row 1 headers in.
0

Author Comment

ID: 36959245

Both your solutions answered the question. I was looking for code that would automate the process but I neglected to put that in the question. Was not expecting to get an equation that would solve the issue. Seems appropriate in this case to split the points.  Thanks again.
0

Author Closing Comment

ID: 36959249
Thanks again.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month17 days, 12 hours left to enroll