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

clearexceldoubts

New Member
I have two columns, containing a person's data on repeated measurements and other one with the presence or absence of a characteristic.

 PatientID Characteristic Yes/No 1 N 1 Y 2 Y 2 Y 2 N 3 N 4 N 5 Y 5 Y 6 N 6 N

<tbody>
</tbody>

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.

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Try:-
Nb:- Your actual Data starts "A2", Results start "C2"
Code:
``````[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

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.

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

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.

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

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.

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
 - A​ B​ 1​ PatientID Characteristic 2​ Yes/No 3​ 1​ N 4​ 1​ Y 5​ 2​ Y 6​ 2​ Y 7​ 2​ N 8​ 3​ N 9​ 4​ N 10​ 5​ Y 11​ 5​ Y 12​ 6​ N 13​ 6​ N
 clearexceldoubts

. 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 )

Code:
``````[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

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.

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

Alan

Replies
1
Views
127
Replies
34
Views
580
Replies
3
Views
129
Replies
1
Views
371
Replies
1
Views
743

1,203,105
Messages
6,053,544
Members
444,670
Latest member
laurenmjones1111

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.

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

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