Count Unique Combinations

[Ron]

Hello all, I'm having problems coming up with a formula to count the
number of unique combinations in a list. I have a list of people in
column B and a list of tasks in column C. There are duplicates in
both columns. I need a formula to count the unique combinations in
the list (i.e. bob smith/j. smith would count 1. Also I need to count
the separtely so I know how many unique combinations are for bob smith
and so on. I hope I've proviede enough info. Thank you for your
assistance, Ron
Col B Col C
bob smith j. smith
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith d. white
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith j. smith
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith j. smith
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion

 

[Ron Rosenfeld]

Hello all, I'm having problems coming up with a formula to count the
number of unique combinations in a list. I have a list of people in
column B and a list of tasks in column C. There are duplicates in
both columns. I need a formula to count the unique combinations in
the list (i.e. bob smith/j. smith would count 1. Also I need to count
the separtely so I know how many unique combinations are for bob smith
and so on. I hope I've proviede enough info. Thank you for your
assistance, Ron
Col B Col C
bob smith j. smith
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith d. white
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith j. smith
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith j. smith
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion

I'm not sure exactly what you want, but consider a Pivot Table.

Drag ColB to the rows area; ColC to the columns area; Col c to the Data or
Value area (and make sure the operation selected is count).

You obtain a result which shows the unique combinations, and the number of
times each combination is present.

--ron

 

[Bernd P]

Hello all, I'm having problems coming up with a formula to count the
number of unique combinations in a list.  I have a list of people in
column B and a list of tasks in column C.  There are duplicates in
both columns.  I need a formula to count the unique combinations in
the list (i.e. bob smith/j. smith would count 1. Also I need to count
the separtely so I know how many unique combinations are for bob smith
and so on.  I hope I've proviede enough info.  Thank you for your
assistance, Ron
Col B              Col C
bob smith       j. smith
don carter      r. jones
alan jackson    s. roberts
jan obryan      g. grigs
june cannon     d. lion
bob smith       d. white
don carter      r. jones
alan jackson    s. roberts
jan obryan      g. grigs
june cannon     d. lion
bob smith       j. smith
don carter      r. jones
alan jackson    s. roberts
jan obryan      g. grigs
june cannon     d. lion
bob smith       j. smith
don carter      r. jones
alan jackson    s. roberts
jan obryan      g. grigs
june cannon     d. lion

Hello,

I suggest to use my UDF Pfreq.
Please see possible options at
http://sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[Ron]

Hi Ron, thanks for your Pivot Table solution, but it's going to be
used by inexperienced users with Excel and needs to auto update the
rollup sheet. I don't think I was very clear in describing what I
actually need. I have a roll up sheet with a unique list of the names
from Column b on a data input sheet. I need the formula to lookup the
names from the unique list on the rollup sheet and count the unique
combinations on the data input sheet.

Bob Smith has 4 unique combos
Don Carter has 3 unique combos
Alan Jackson has 1 unique combo

Unique list on rollup sheet
bob smith
don carter
alan jackson
jan obryan
june cannon

Data input sheet
Col B Col C
bob smith j. smith 1
don carter r. jones 1
alan jackson s. roberts 1
jan obryan g. grigs
june cannon d. lion
bob smith d. white 1
don carter r. wilson 1
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith s. lapin 1
don carter t. wright 1
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith j. jones 1
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion

Thank you for your assistance, Ron

 

[Ron Rosenfeld]

Hi Ron, thanks for your Pivot Table solution, but it's going to be
used by inexperienced users with Excel and needs to auto update the
rollup sheet. I don't think I was very clear in describing what I
actually need. I have a roll up sheet with a unique list of the names
from Column b on a data input sheet. I need the formula to lookup the
names from the unique list on the rollup sheet and count the unique
combinations on the data input sheet.

Bob Smith has 4 unique combos
Don Carter has 3 unique combos
Alan Jackson has 1 unique combo

Unique list on rollup sheet
bob smith
don carter
alan jackson
jan obryan
june cannon

Data input sheet
Col B Col C
bob smith j. smith 1
don carter r. jones 1
alan jackson s. roberts 1
jan obryan g. grigs
june cannon d. lion
bob smith d. white 1
don carter r. wilson 1
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith s. lapin 1
don carter t. wright 1
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion
bob smith j. jones 1
don carter r. jones
alan jackson s. roberts
jan obryan g. grigs
june cannon d. lion

Thank you for your assistance, Ron

One simple method is to use a User Defined Function. Given your layout with
the Unique list already extracted, and the tasks in the column next to the name
on the Data Input sheet, I would do the following:

1. Define a range called Names to be Col B on the Data Input sheet. I would
use a dynamic range definition, but you could just use a "large enough" range.

2. To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=Uniques(A1,Names)

in some cell.

============================================
Option Explicit
Function Uniques(UniqueName As String, Names As Range) As Long
Dim c As Range
Dim t As New Collection
Dim i As Long

On Error Resume Next
For Each c In Names
If c = UniqueName Then
t.Add Item:=c.Offset(0, 1).Value, Key:=c.Offset(0, 1).Value
End If
Next c
On Error GoTo 0
Uniques = t.Count
End Function
===================================

--ron

 

[Ron]

Hi Ron, Wow! thanks that worked perfect. I going to learn more about
using functions in these situations. Thank you again, Ron

 

[Ron Rosenfeld]

Hi Ron, Wow! thanks that worked perfect. I going to learn more about
using functions in these situations. Thank you again, Ron

You're welcome. Thanks for the feedback.

While a formula solution may be possible, frequently it's simpler to come up
with a UDF.

By the way, I neglected to mention that the A1 argument in the UDF refers to
the unique name on your Roll Up sheet; but I guess you figured it out.

--ron