Best way to group survey results by ward reporting % of Yes results

roster

New Member
Joined
Apr 14, 2006
Messages
20
Not sure how to do this.

worksheet has the results of and audit. Y is that the ward was compliant for that question, N no, N/A and Unknown

column A has the wards audited
columns B-A0 have the answers to the survey Questions in the form of Y N N/A and Unknown


Eg
A B C
Ward Was the ID checked Consent valid...……………...
2a Y N
1f Y N
2a Y N/A
2d Y N
2a Y N
2a Y N

How collate the data in a new worksheet, group by ward and produce a table with number of audits and the percentage of Compliant (Y) responses?
example

Ward Number of Audits Was ID checked Consent valid
2a 4 100% 0%
2d etc
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sorry to ask but what happens when a result is N/A or Unknown? how does this effect the percentage?
 
Upvote 0
How then do you distinguish between No ? which is also a 0.. This would give results that suggest non compliance instead there was just an unknown or N/A value.
 
Upvote 0
The code only calculates between "y" & "n" every thing else is "0"
Ref your requirement:-
Almost there. looks like the % are incorrect as there are n/a and unknown results. Could we assign a 0 value to these and have a percentage calculation like this yes/(yes+no)*100??

A particular "Ward/column" could have a series of "y" and "n" and "n/a" and "Unknown", but the code does not relate to the "n/a" or "Unknown", only the "y/n". If there are no "y/n" it will return a 0.

How would you like to deal with that situation ????
 
Upvote 0
I'm not sure... perhaps the calculation I gave you is not the way to go.

Currently with your code if a column has all "n" if gives a 0
Also if all column answers are "n/a" or "unknown" it gives a 0
How do we distinguish between these?

Essentially I am determining compliance of an audit as a percentage due to different criteria. I need to differentiate between all n- which would be a fail and "N/A" and "unknown" as not enough data to determine.

I have spent weeks of time trying to solve this by pivot tables.. I just think I m not smart enough to get this. I appreciate your help Mick.
 
Last edited:
Upvote 0
Try this:-
Results on Sheet "Result2".
NB:- Each column of the results ae double up, so the first column is "y/(y+n) and the second is the percentage of the whole that is anything else i.e. "N/A, Unknown or blank". !!

Code:
[COLOR=navy]Sub[/COLOR] MG22Jul51
'[COLOR=green][B]forum 4 (forum)[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Q [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant, col [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nAc [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] t, tt
[COLOR=navy]With[/COLOR] Sheets("Data")
[COLOR=navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
   
   ReDim nray(1 To 41, 1 To 3)
    [COLOR=navy]For[/COLOR] n = 3 To UBound(nray, 1)
        
        [COLOR=navy]If[/COLOR] UCase(Dn.Offset(, n - 2).Value) = "Y" [COLOR=navy]Then[/COLOR] nray(n, 1) = 1
        [COLOR=navy]If[/COLOR] UCase(Dn.Offset(, n - 2).Value) = "Y" Or UCase(Dn.Offset(, n - 2).Value) = "N" [COLOR=navy]Then[/COLOR]
            nray(n, 2) = 1
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]If[/COLOR] Not UCase(Dn.Offset(, n - 2).Value) = "Y" And Not UCase(Dn.Offset(, n - 2).Value) = "N" [COLOR=navy]Then[/COLOR]
            nray(n, 3) = 1
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
    .Item(Dn.Value) = Array(nray, 1)
[COLOR=navy]Else[/COLOR]
    Q = .Item(Dn.Value)
    
    [COLOR=navy]For[/COLOR] n = 3 To UBound(Q(0))
       [COLOR=navy]If[/COLOR] UCase(Dn.Offset(, n - 2).Value) = "Y" [COLOR=navy]Then[/COLOR] Q(0)(n, 1) = Q(0)(n, 1) + 1
       [COLOR=navy]If[/COLOR] UCase(Dn.Offset(, n - 2).Value) = "Y" Or UCase(Dn.Offset(, n - 2).Value) = "N" [COLOR=navy]Then[/COLOR]
            Q(0)(n, 2) = Q(0)(n, 2) + 1
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]If[/COLOR] Not UCase(Dn.Offset(, n - 2).Value) = "Y" And Not UCase(Dn.Offset(, n - 2).Value) = "N" [COLOR=navy]Then[/COLOR]
            Q(0)(n, 3) = Q(0)(n, 3) + 1
        [COLOR=navy]End[/COLOR] If
    
    [COLOR=navy]Next[/COLOR] n
    
    
    Q(1) = Q(1) + 1
    .Item(Dn.Value) = Q
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
 ReDim ray(1 To .Count + 2, 1 To 80)
     ray(1, 1) = Rng(1).Offset(-1)
     ray(1, 2) = "Count of Audits"
        [COLOR=navy]For[/COLOR] ac = 3 To UBound(ray, 2) [COLOR=navy]Step[/COLOR] 2
            nAc = nAc + 1
            ray(1, ac) = Rng(1).Offset(-1, nAc)
            ray(2, ac) = "y/(y+n)"
            ray(1, ac + 1) = Rng(1).Offset(-1, nAc)
             ray(2, ac + 1) = "Other"
        [COLOR=navy]Next[/COLOR] ac
c = 2

[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .Keys
     c = c + 1
     nAc = 2
     
     ray(c, 1) = K
     ray(c, 2) = .Item(K)(1)
       [COLOR=navy]For[/COLOR] ac = 3 To UBound(ray, 2) [COLOR=navy]Step[/COLOR] 2
         
          nAc = nAc + 1
              
         [COLOR=navy]If[/COLOR] Not IsEmpty(.Item(K)(0)(nAc, 2)) [COLOR=navy]Then[/COLOR]
                    ray(c, ac) = Format(.Item(K)(0)(nAc, 1) / .Item(K)(0)(nAc, 2), "0%")
                [COLOR=navy]End[/COLOR] If
            
               
               [COLOR=navy]If[/COLOR] Not IsEmpty(.Item(K)(0)(nAc, 3)) [COLOR=navy]Then[/COLOR]
                    ray(c, ac + 1) = Format(.Item(K)(0)(nAc, 3) / .Item(K)(1), "0%")
                [COLOR=navy]End[/COLOR] If
    col = col + 1
    [COLOR=navy]Next[/COLOR] ac
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Sheets("Results2").Range("A1").Resize(c, UBound(ray, 2))
    .Parent.Cells.NumberFormat = "general"
    .Value = ray
    .Borders.Weight = 2
    .Cells.ColumnWidth = 7
    .Parent.Rows(1).WrapText = True
    .Parent.Rows(1).AutoFit
[COLOR=navy]End[/COLOR] With
MsgBox "End"
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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