Count Unique Combinations
in [Programming]
|
From: Ron on 15 May 2010 13:04 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...(a)nospam.org> wrote: > On Sat, 15 May 2010 07:55:00 -0700 (PDT), Ron <whasupwit...(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- Hide quoted text - > > - Show quoted text -
From: Ron Rosenfeld on 15 May 2010 13:35
On Sat, 15 May 2010 10:04:21 -0700 (PDT), Ron <whasupwityou(a)gmail.com> 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 |