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

On Fri, 14 May 2010 15:53:24 -0700 (PDT), Ron <(E-Mail Removed)> wrote:

>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

On 14 Mai, 23:53, Ron <whasupwit...@gmail.com> wrote:
> 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

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

On May 14, 5:09*pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Fri, 14 May 2010 15:53:24 -0700 (PDT), Ron <whasupwit...@gmail.com> wrote:
> >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- Hide quoted text -
>
> - Show quoted text -

On Sat, 15 May 2010 07:55:00 -0700 (PDT), Ron <(E-Mail Removed)> wrote:

>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

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



On May 15, 9:08*am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Sat, 15 May 2010 07:55:00 -0700 (PDT), Ron <whasupwit...@gmail.com> wrote:
> >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 layoutwith
> 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- Hide quoted text -
>
> - Show quoted text -

On Sat, 15 May 2010 10:04:21 -0700 (PDT), Ron <(E-Mail Removed)> wrote:

>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