?
Solved

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

Posted on 2011-10-12
16
Medium Priority
?
224 Views
Last Modified: 2012-08-13
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
Comment
Question by:PDSWSS
  • 7
  • 6
  • 3
16 Comments
 
LVL 24

Accepted Solution

by:
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

Open in new window

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:PDSWSS
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:StephenJR
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

by:PDSWSS
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

by:PDSWSS
ID: 36958529
StephenJR

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

Author Comment

by:PDSWSS
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

by:Saqib Husain, Syed
ID: 36958738
Actually you would have to fill row 1 and column 1 before applying the formula.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:Saqib Husain, Syed
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

by:Saqib Husain, Syed
ID: 36958811
No need to enter column1 or row 1 seperately
0
 

Author Comment

by:PDSWSS
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

by:Saqib Husain, Syed
ID: 36959099
You have to enter the formula on the result sheet not the data sheet.
0
 
LVL 24

Expert Comment

by:StephenJR
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

by:PDSWSS
ID: 36959245
Thanks for your expert input.

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

by:PDSWSS
ID: 36959249
Thanks again.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question