Counting Unique Values by Person

[mathewrtaylor]

Hey Team, I've been racking my brain on this, and either I'm over complicating or overlooking it.

Here's the scenario:

Addy 1
Addy 2
Addy 1
Addy 4
Addy 4
Addy 3
Bart 1
Bart 1

I'd like to count the number of unique values (regardless of what the numbers are). The desired output is: Addy 4, Bart 1. I've been trying to see how to do this, but other than adding an identifier along the original data (which I don't particularly want to do) and adding that number, I can't see an alternative. Any suggestions or recommendations?

This site has been a primary source of knowledge for me over the years and the first time I couldn't find an answer, so finally registered!

Mathew

 

[Joe4]

Welcome to the Board!

One way would be to use the Remove Duplicates functionality to remove all the duplicates, and then count the remaining names.
You might be able to use Pivot Tables or Subtotals to get those counts for each person without having to create a formula for each one or use VBA.

 

[Weazel]

maybe something like...


E2 =SUM(IF(FREQUENCY(IF(($A$2:$A$9=D2)*($B$2:$B$9<>""),$B$2:$B$9),$B$2:$B$9),1)) Control shift enter

[TABLE="class: grid, width: 240"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]B
[/TD]
[TD="width: 64, bgcolor: transparent"]C
[/TD]
[TD="width: 64, bgcolor: transparent"]D
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]E
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]2
[/TD]
[TD="width: 64, bgcolor: transparent"]Addy
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Addy
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]Addy
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Bart
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]Addy
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5
[/TD]
[TD="bgcolor: transparent"]Addy
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]Addy
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7
[/TD]
[TD="bgcolor: transparent"]Addy
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"]Bart
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"]Bart
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[mathewrtaylor]

Welcome to the Board!

One way would be to use the Remove Duplicates functionality to remove all the duplicates, and then count the remaining names.
You might be able to use Pivot Tables or Subtotals to get those counts for each person without having to create a formula for each one or use VBA.

I'm really trying not to do any destructive editing, so trying to avoid VBE though certainly a last ditch effort. Thanks for the suggestion!

Mathew

 

[mathewrtaylor]

maybe something like...


E2 =SUM(IF(FREQUENCY(IF(($A$2:$A$9=D2)*($B$2:$B$9<>""),$B$2:$B$9),$B$2:$B$9),1)) Control shift enter

[TABLE="class: grid, width: 240"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]E[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"]Addy[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Addy[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Addy[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Bart[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Addy[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Addy[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]Addy[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]Addy[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]Bart[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]Bart[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

This works like a charm! This is an area where I don't have a great breadth of work; array formulas. Thanks greatly for the suggestion, solved my issue!

Mathew

 

[Weazel]

you're welcome, glad it worked for you

 

[Joe4]

Weazel's suggestion does not involve any VBA or deleting/removing any data.
What might be a pain is coming up with the list of unique names in column D (if your data is quite long).
However, there is a quick formulaic way you can do that too. See: How to extract a unique distinct list from a column in excel | Get Digital Help - Microsoft Excel resource

So if you combine that with Weazel's suggestion, you should be able to get what you want without any VBA or without having to delete out any data rows.