How do I get the number of unique cases used to get the frequency of the other variable using Excel

[clearexceldoubts]

I have two columns, containing a person's data on repeated measurements and other one with the presence or absence of a characteristic.
Data reads like this;


[TABLE="width: 50"]
<tbody>[TR]
[TD]PatientID[/TD]
[TD]Characteristic
Yes/No[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

I want the number of unique cases used to get the frequencies in other variable. For Ex:
Y - 5 (from 3 cases/patients)
N - 6 (from 5 cases/patients).
How do I do this using Excel. I have several characteristics like this.

 

[MickG]

Try:-
Nb:- Your actual Data starts "A2", Results start "C2"

[COLOR="Navy"]Sub[/COLOR] MG02Jun41
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, k [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
                Dic(Dn.Value)(Dn.Offset(, -1).Value) = Empty
[COLOR="Navy"]Next[/COLOR] Dn
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        c = c + 1
        Cells(c, "C") = k & " = " & Application.CountIf(Rng, k) & " (From " & Dic(k).Count & " Cases/Patients"
        Cells(c, "C").Columns.AutoFit
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mike LH]

Hi,

To count the Y

=COUNTIF(B2:B20,"Y")

and the N

=COUNTIF(B2:B20,"N")

And for unique ID's with Y & N try these 2 ARRAY formula, see below for how to enter them.

=COUNT(IF(FREQUENCY(IF(B2:B20="Y",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(B2)+1),A2:A20))

and

=COUNT(IF(FREQUENCY(IF(B2:B20="N",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(B2)+1),A2:A20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[clearexceldoubts]

I'm relatively new to Excel Macros and all. However, I'll give a try to this Sir. Appreciate your response and efforts. Thank You

 

[clearexceldoubts]

Hello Sir,
This is working perfectly fine for my data. Thanks for this easy formula. Thanks tons.

Hi,

To count the Y

=COUNTIF(B2:B20,"Y")

and the N

=COUNTIF(B2:B20,"N")

And for unique ID's with Y & N try these 2 ARRAY formula, see below for how to enter them.

=COUNT(IF(FREQUENCY(IF(B2:B20="Y",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(B2)+1),A2:A20))

and

=COUNT(IF(FREQUENCY(IF(B2:B20="N",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(B2)+1),A2:A20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[clearexceldoubts]

I'm relatively new to Excel Macros and all. However, I'll give a try to this Sir. Appreciate your response and efforts. Thank You

 

[DocAElstein]

@ clearexceldoubts

Hi, here is the beginner, straggling again with his attempt…

I have two columns, containing a person's data on repeated measurements and other one with the presence or absence of a characteristic.
Data reads like this………..

I want the number of unique cases used to get the frequencies in other variable. For Ex:
Y - 5 (from 3 cases/patients)
N - 6 (from 5 cases/patients).
How do I do this using Excel. I have several characteristics like this.

.. I assume your spread sheet looms like this ( It is how it copied into a spreadsheet by me )

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]PatientID[/td][td]Characteristic[/td][/tr]

[tr][td]

2​

[/td][td][/td][td]Yes/No[/td][/tr]

[tr][td]

3​

[/td][td]

1​

[/td][td]N[/td][/tr]

[tr][td]

4​

[/td][td]

1​

[/td][td]Y[/td][/tr]

[tr][td]

5​

[/td][td]

2​

[/td][td]Y[/td][/tr]

[tr][td]

6​

[/td][td]

2​

[/td][td]Y[/td][/tr]

[tr][td]

7​

[/td][td]

2​

[/td][td]N[/td][/tr]

[tr][td]

8​

[/td][td]

3​

[/td][td]N[/td][/tr]

[tr][td]

9​

[/td][td]

4​

[/td][td]N[/td][/tr]

[tr][td]

10​

[/td][td]

5​

[/td][td]Y[/td][/tr]

[tr][td]

11​

[/td][td]

5​

[/td][td]Y[/td][/tr]

[tr][td]

12​

[/td][td]

6​

[/td][td]N[/td][/tr]

[tr][td]

13​

[/td][td]

6​

[/td][td]N[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]clearexceldoubts[/td][/tr][/table]

. The following code puts the unique count for Y and N in number variables and the patients ID for those in Array Variables.
. Let me know if you need detailed explanation.

Alan

Code:- (Very similar idea to Mike G’s, I think )

[color=blue]Sub[/color] clearexceldoubtsUniques()
 
[color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("clearexceldoubts")
[color=blue]Dim[/color] r [color=blue]As[/color] [color=blue]Long[/color], lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks1.Cells.Find(what:="*", After:=wks1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[color=blue]Dim[/color] dicOb1 [color=blue]As[/color] [color=blue]Object[/color], z [color=blue]As[/color] [color=blue]Variant[/color]
[color=blue]Set[/color] dicOb1 = CreateObject("Scripting.Dictionary")
[color=blue]Dim[/color] dicOb2 [color=blue]As[/color] [color=blue]Object[/color]
[color=blue]Set[/color] dicOb2 = CreateObject("Scripting.Dictionary")
[color=blue]Dim[/color] YKeys() [color=blue]As[/color] [color=blue]Variant[/color], NKeys() [color=blue]As[/color] [color=blue]Variant[/color], CntN [color=blue]As[/color] [color=blue]Long[/color], CntY [color=blue]As[/color] [color=blue]Long[/color]
 
[color=lightgreen]'Dim dicOb1 As Scripting.Dictionary, z As Variant[/color]
[color=lightgreen]'Set dicOb1 = New Scripting.Dictionary[/color]
[color=lightgreen]'Dim dicOb2 As Scripting.Dictionary[/color]
[color=lightgreen]'Set dicOb2 = New Scripting.Dictionary[/color]
 
    [color=blue]For[/color] r = 3 [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]' go down each row starting at row 3[/color]
        [color=blue]If[/color] wks1.Cells(r, 2).Value = "Y" [color=blue]Then[/color] [color=lightgreen]'Check for Y in column B[/color]
        [color=blue]Let[/color] z = dicOb1.Item(wks1.Cells(r, 1).Value)
        [color=blue]Else[/color] [color=lightgreen]'If no Y in column B then check for N in column B[/color]
            [color=blue]If[/color] wks1.Cells(r, 2).Value = "N" [color=blue]Then[/color] [color=lightgreen]'Check for N in column B[/color]
            [color=blue]Let[/color] z = dicOb2.Item(wks1.Cells(r, 1).Value)
            [color=blue]Else[/color] [color=lightgreen]'Case Neither Y or N, so do nothing . Redundant code[/color]
            [color=blue]End[/color] [color=blue]If[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] r [color=lightgreen]'go to next row[/color]
   
[color=lightgreen]' Output - Count of Y/N : Array with corresoonding Patients ID[/color]
[color=blue]Let[/color] CntY = dicOb1.Count: [color=blue]Let[/color] YKeys() = dicOb1.Keys
[color=blue]Let[/color] CntN = dicOb2.Count: [color=blue]Let[/color] NKeys() = dicOb2.Keys
 
 [color=blue]Set[/color] dicOb1 = [color=blue]Nothing[/color]
 [color=blue]Set[/color] dicOb2 = [color=blue]Nothing[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

…………………………………………………………………

…………………………………………

@ Mike G

. If You pop back to this Thread , could You explain what this does please..

Dic.CompareMode = 1


Thanks
Alan

 

[MickG]

Hi Alan, If you look at the VB Hep for "CompareMode", there's a full explanation.
It basically determines if the values are case sensitive.

 

[DocAElstein]

Hi Alan, If you look at the VB Hep for "CompareMode", there's a full explanation.

Hi Mark,
. In the F2 help search window I did not find CompareMode, - Then I googled a bit and got the point that your
.CompareMode = 1
is equivalent to the
.CompareMode = vbTextCompare
which I have usually seen in similar codes. Along the way I highlighted then a vbTextCompare which was in a code of mine and hit F1. I came up with the same help window as before but this time it listed the 4 different variations, but did not particularly explaine anything really well. !?
. - A lot of experienced users have told me The VBA Help is really crap and that you usually need to know what the answer is to know where / what to look for... I see what they mean now,..!!. I did not quite see anything along your “....determines if the values are case sensitive…. „ But I will have that in mind when I experiment a bit, it could then help for it to sink in with me what it means..

So thanks for the reply..

Alan