Count Unique Combinations
in [Programming]
|
From: Ron on 14 May 2010 18:53 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
From: Ron Rosenfeld on 14 May 2010 20:09 On Fri, 14 May 2010 15:53:24 -0700 (PDT), Ron <whasupwityou(a)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
From: Bernd P on 15 May 2010 00:57 On 14 Mai, 23:53, Ron <whasupwit...(a)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
From: Ron on 15 May 2010 10:55 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...(a)nospam.org> wrote: > On Fri, 14 May 2010 15:53:24 -0700 (PDT), Ron <whasupwit...(a)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 -
From: Ron Rosenfeld on 15 May 2010 12:08
On Sat, 15 May 2010 07:55:00 -0700 (PDT), Ron <whasupwityou(a)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 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 |