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

[roster]

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

 

[roster]

Hi Mick!! Thanks again for replying... except for the audit count , Unfortunately the results are coming up as 0.. any suggestions?

My mistake it is running perfectly!

Thank you again

 

[roster]

Sorry to ask but what happens when a result is N/A or Unknown? how does this effect the percentage?

 

[MickG]

"N/A" and "Unknown" should return 0

 

[roster]

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.

 

[MickG]

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 ????

 

[roster]

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.

 

[MickG]

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". !!

[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