Excel Formula or VBA if necessary

C

Claret

New Member
Joined
Mar 6, 2017
Messages
14
Hello,

I have a table of data (shortened example below) with 40+ questions and 500 + respondents.

The green section of the table indicates the questions that were answered incorrectly by an individual and the number of times they answered the question incorrectly (these are quality checks so the same questions are checked multiple times)

I'd like to be able to consolidate the data (via either formula or simple code) so that for each person, just the incorrect questions and the number of times incorrect are shown (as per the example in the red section of the table). Effectively ignoring those where there are no errors and returning a horizontal list of errors in separate cells with no blanks.

Any help would be much appreciated.

David



[TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl67, width: 55, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu1
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu2
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu3
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu4
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu5
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu6
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu7
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu8
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu9
[/TD]
[TD="class: xl68, width: 40, bgcolor: #EBF1DE"]Qu10
[/TD]
[TD="class: xl66, width: 61, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 61, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 61, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 61, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #EBF1DE"]Person 1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]2
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu1 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu3 (2)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu5 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu7 (1)
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #EBF1DE"]Person 2
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]2
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu2 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu6 (2)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu9 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu10 (1)
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #EBF1DE"]Person 3
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #EBF1DE"]Person 4
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu4 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #EBF1DE"]Person 5
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]3
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"]1
[/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl68, bgcolor: #EBF1DE"][/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu3 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu5 (3)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"]Qu8 (1)
[/TD]
[TD="class: xl69, bgcolor: #F2DCDB"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
I've amended the code as stated but this sees the column headers for any questions that are incorrect added followed by a line of white space and then the count of instances for the header (this is instead of the question number and number of instances as was showing previously).

Dim lastRow As Long
Dim myRow As Long
Dim myCol As Long
Dim popCol As Long


Application.ScreenUpdating = False

' Find last row with data in column A
lastRow = Cells(Rows.Count, "B").End(xlUp).Row



' Loop through all rows
For myRow = 3 To lastRow
' Set first column to populate
popCol = 63
' Loop through columns B-K
For myCol = 4 To 60
' If value is greater than zero, write to end of table
If Cells(myRow, myCol) > 0 Then
Cells(myRow, popCol) = Trim(Cells(2, myCol)) & Chr(10) & Cells(myRow, myCol)

' Increment popCol
popCol = popCol + 1
End If
Next myCol
Next myRow

Application.ScreenUpdating = True

End Sub
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I am not sure I understand what you are telling me.
Could you show me some picture examples like you did in your first post?
 
Upvote 0
If you'd like to try it with a formula, put this in BJ2, then copy it along and down as far as required:
Code: 
=IF(COUNT($B2:$BI2)>COLUMN()-COLUMN($BJ:$BJ),INDEX(OFFSET($B$1:$BI$1,,IF(COLUMN()=COLUMN($BJ:$BJ),0,MATCH(TRIM(LEFT(BI2,4)),$B$1:$BI$1,0))),MATCH(TRUE,INDEX(ISNUMBER(OFFSET($B2:$BI2,,IF(COLUMN()=COLUMN($BJ:$BJ),0,MATCH(TRIM(LEFT(BI2,4)),$B$1:$BI$1,0)))),),0))&" ("&INDEX(OFFSET($B2:$BI2,,IF(COLUMN()=COLUMN($BJ:$BJ),0,MATCH(TRIM(LEFT(BI2,4)),$B$1:$BI$1,0))),MATCH(TRUE,INDEX(ISNUMBER(OFFSET($B2:$BI2,,IF(COLUMN()=COLUMN($BJ:$BJ),0,MATCH(TRIM(LEFT(BI2,4)),$B$1:$BI$1,0)))),),0))&")","")
This will need some adjustment if you end up with more than 99 questions, otherwise it should be fine. However, VBA will probably perform much faster.
 
Upvote 0
Just for information - row 1 is completely blank and all data starts in row two:

This is with the revised trim code:

[TABLE="width: 566"]
<colgroup><col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;" span="2"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="5"> <tbody>[TR]
[TD="class: xl66, width: 159, bgcolor: #E4DFEC"]Advisor Name[/TD]
[TD="class: xl66, width: 75, bgcolor: #E4DFEC"]Error 1[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 2[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 3[/TD]
[TD="class: xl66, width: 73, bgcolor: #E4DFEC"]Error 4[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 5[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 6[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 7[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 8[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 9[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #F2DCDB"]Aitoro, Richard[/TD]
[TD="class: xl70, width: 75, bgcolor: white"]REG: When registering a client, capture if they are on an active debt solution or if they've tried other debt solutions in the past. If relevant, note why these didn't succeed
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]Capture the referral source accurately
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]REG: Discuss and agree priority arrears arrangements if applicable
 1[/TD]
[TD="class: xl71, width: 73, bgcolor: white"]REG: Advise the client on the detailed facts in the Gateway for the solution they have chosen. Cover bank account advice
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]REG: Give the client appropriate collections advice
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]Address any client concerns and provide details of any key next steps if appropriate
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]Accurately result every call taken
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]Accurately reflect important information and key discussion points in the notes
 1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]Add correct and meaningful additional comments into the PAP
 1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]Open the call appropriately
 Q1 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]Capture all relevant information relating to the Household screen accurately
 Q11 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]Create a budget that is collaborative, realistic and clear
 Q19 1[/TD]
[TD="class: xl73, width: 73, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]Create a budget that is collaborative, realistic and clear
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]Capture all relevant information relating to the Debts screen accurately, including guarantor and guaranteed debts, disputed debts and statute barred debts
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]Inform the client that costs listed as priority payments are essential and should be maintained before other payments
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]REG: Give the client appropriate collections advice
 1[/TD]
[TD="class: xl73, width: 73, bgcolor: white"]Address any client concerns and provide details of any key next steps if appropriate
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]Accurately reflect important information and key discussion points in the notes
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]Add correct and meaningful additional comments into the PAP
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]Capture all relevant information relating to the Priority Expenditure screen accurately
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]REG: Give the client appropriate collections advice
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]Accurately reflect important information and key discussion points in the notes
 1[/TD]
[TD="class: xl74, width: 73, bgcolor: white"]REG: Provide the appropriate and correct advice and/or information to the client, making sure the client understands this
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl75, width: 75, bgcolor: white"]Inform the client that costs listed as priority payments are essential and should be maintained before other payments
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]Inform the client that costs listed as priority payments are essential and should be maintained before other payments
 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]


This is with the original code - which is working as I wanted with the exception of there being a line of white space ahead of the data in each cell.

[TABLE="width: 566"]
<colgroup><col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;" span="2"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="5"> <tbody>[TR]
[TD="class: xl66, width: 159, bgcolor: #E4DFEC"]Advisor Name[/TD]
[TD="class: xl66, width: 75, bgcolor: #E4DFEC"]Error 1[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 2[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 3[/TD]
[TD="class: xl66, width: 73, bgcolor: #E4DFEC"]Error 4[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 5[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 6[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 7[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 8[/TD]
[TD="class: xl66, width: 64, bgcolor: #E4DFEC"]Error 9[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: #F2DCDB"]Aitoro, Richard[/TD]
[TD="class: xl70, width: 75, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl71, width: 73, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl70, width: 64, bgcolor: white"]
1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]
Q1 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
Q11 1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
Q19 1[/TD]
[TD="class: xl73, width: 73, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 73, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl73, width: 75, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl73, width: 64, bgcolor: white"]
1[/TD]
[TD="class: xl74, width: 73, bgcolor: white"]
1
[/TD]
[TD="class: xl73, width: 64, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
which is working as I wanted with the exception of there being a line of white space ahead of the data in each cell.
Click to expand...
Are you sure that is what is actually going on, and it isn't just a formatting issue?
Note that if a row widths expands to account for carriage returns and data wrapping, the default format option for Vertical Alignment is "Bottom", which means the data is the bottom, so the white space appears at the top and not the bottom of a cell. You can change that to "Top" or "Center", if you don't like the way it looks.
 
Upvote 0
Hi Joe

Yes - expanding the column widths, with the text at the top of the cell and ensuring wrapping is removed still sees a full row of white space ahead of the result.

Cheers

David
 
Upvote 0
That's some formula stunrock!! Thanks for this - I'll stick with the VBA option due to filesize but will have a look at that formula - thanks again.

David
 
Upvote 0
Hi Joe4

I've amended the code slightly (changing the CHR from 10 to 42) which puts a blank space in, instead of a new row.

The only issue that I have now is that the codes is returning the full question for example; QU18.0 REG: QQQQQQQQ 1

What I need it to return is QU18.0 1 (with the 1 being a changeable number depending on how many times this particular question may have been answered incorrectly... The QU18.0 could of course change depending on which questions were incorrect.

Any further help would be much appreciated.

David

Dim lastRow As Long
Dim myRow As Long
Dim myCol As Long
Dim popCol As Long


Application.ScreenUpdating = False

' Find last row with data in column B
lastRow = Cells(Rows.Count, "B").End(xlUp).Row



' Loop through all rows
For myRow = 3 To lastRow
' Set first column to populate
popCol = 63
' Loop through columns B-K
For myCol = 4 To 60
' If value is greater than zero, write to end of table
If Cells(myRow, myCol) > 0 Then
Cells(myRow, popCol) = Trim(Cells(2, myCol)) & Chr(32) & Cells(myRow, myCol)

' Increment popCol
popCol = popCol + 1
End If
Next myCol
Next myRow

Range("BK3:CD600").Select
Range("CD3").Activate
Dim myRow As Long
Dim myCol As Long
Dim popCol As Long


Application.ScreenUpdating = False
 
Upvote 0
The only issue that I have now is that the codes is returning the full question for example; QU18.0 REG: QQQQQQQQ 1

What I need it to return is QU18.0 1 (with the 1 being a changeable number depending on how many times this particular question may have been answered incorrectly... The QU18.0 could of course change depending on which questions were incorrect.
Click to expand...
Not sure I follow, but this really appears to be a new question (though it may be on the same project), so you are probably best off posting it to a new thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
930
Fluff
Fluff
S
  • Solved
Table comparison using formulas
Replies
2
Views
113
sharshra
S
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
K
  • Solved
Excel VBA prompt for cells, then use in relative formula
Replies
2
Views
319
kitty111222
K
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
608
Domenic
D

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top