Count unique values multiple conditions

[liybpg]

I have a large dataset and I need to count the number of unique entries in one column, if it belongs to one group or another (it is indicated in another column, e.g. it may give group ID of 23 or 34 or 193 etc). Ideally, it would be a formula which I can use across many entries (need to do this 1000+ times)

Anyone has any thoughts?

 

[Aladin Akyurek]

Try to post a small illustrative sample along with the results you want to obtain.

 

[liybpg]

Hi Aladin,

Thanks for the suggestion, please find it below (sorry cant see how to attach files).

What I want to do is to show, for example, the number of unique values of Names that are in category 1. Please bear in mind that this is a vastly simplified version, so I can't sort or rearrange data, ideal solution would be a formula.

Thank you!

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]Category[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]BNM[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]BNM[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]XYZ[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Are the category values indeed integers?

 

[Mike LH]

Hi,

Try this ARRAY formula, see below for how to enter it. The group category is in D1 and the formula can be dragged.

=COUNT(1/FREQUENCY(IF(B$1:B$100=D1,MATCH(A$1:A$100,A$1:A$100,0)),ROW(A$1:A$100)-ROW(A$1)+1))

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.

 

[liybpg]

Yes, they are

 

[liybpg]

Mike, this is great, thank you for you help with that! Took me couple of hours to come up with nothing

Aladin thanks for your attention to this as well

Hi,

Try this ARRAY formula, see below for how to enter it. The group category is in D1 and the formula can be dragged.

=COUNT(1/FREQUENCY(IF(B$1:B$100=D1,MATCH(A$1:A$100,A$1:A$100,0)),ROW(A$1:A$100)-ROW(A$1)+1))

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.

 

[Aladin Akyurek]

Yes, they are

Thanks.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

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

1​

[/td][td]Name[/td][td]Category[/td][td][/td][td]

2​

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

[tr][td]

2​

[/td][td]ABC[/td][td]

1​

[/td][td][/td][td]#Category[/td][td]#Unique name count[/td][/tr]

[tr][td]

3​

[/td][td]ABC[/td][td]

2​

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

1​

[/td][td]

3​

[/td][/tr]

[tr][td]

4​

[/td][td]XYZ[/td][td]

1​

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

2​

[/td][td]

3​

[/td][/tr]

[tr][td]

5​

[/td][td]BNM[/td][td]

2​

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

[tr][td]

6​

[/td][td]ABC[/td][td]

2​

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

[tr][td]

7​

[/td][td]XYZ[/td][td]

2​

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

[tr][td]

8​

[/td][td]BNM[/td][td]

1​

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

[tr][td]

9​

[/td][td]ABC[/td][td]

1​

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

[tr][td]

10​

[/td][td]XYZ[/td][td]

1​

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

[tr][td]

11​

[/td][td]ABC[/td][td]

1​

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

D1, just enter:

=SUM(IF(FREQUENCY(B2:B11,B2:B11),1))

D3, control+shift+enter (cse), not just enter, and copy down:

=IF(ROWS($D$3:D3)<=$D$1,MIN(IF(ISNUMBER(MATCH($B$2:$B$11,$D$2:D2,0)),"",$B$2:$B$11)),"")

E3, cse and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$11<>"",IF($B$2:$B$11=$D3,
    MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))),ROW($A$2:$A$11)-ROW($A$2)+1),1))

note. You might want to remove the "~"& and &"" bits if there are no special meaning chars like < aroun the names.

 

[liybpg]

Aladin, thanks for great suggestions, I will test them out when get back to my dataset