Count first instance of two unique entries

Kinno · Mar 29, 2019

Hi all! This is my first post on here so go easy on me! I've searched all over the web and in this forum for a solution but I can't seem to find anything explaining what I'm really trying to do. Been stuck on this for a few days so now I'm coming to ask for help!

So I have a spreadsheet full of patients appointments and who they were referred to the company by. I want to try and count the number of times a referral source has provided a new patient to the company. My list however contains all the appointments these people have had and therefore the referral source is listed each time. This isn't ideal as I only need to count the referral source the one time per patient. I've tried all kinds of different things and nothing seems to work for me sadly.

So the data looks as follows

[TABLE="width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Mario[/TD]
[TD]Insurer[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Clinician[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Website[/TD]
[/TR]
[TR]
[TD]Liam[/TD]
[TD]GP[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]GP[/TD]
[/TR]
[TR]
[TD]Alexi[/TD]
[TD]Clinician[/TD]
[/TR]
[TR]
[TD]Rob[/TD]
[TD]Website[/TD]
[/TR]
[TR]
[TD]Ana[/TD]
[TD]Clinician[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]GP[/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD]GP[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Clinician[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD]GP[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Website[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]Website[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 300"]
<tbody>[TR]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Clinician[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Insurer[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Website[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]GP[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

So hopefully someone can help me out with this

Should count
Clinician: 3
Insurer: 1
GP: 4
Website: 2

Thanks

Marcelo Branco · Mar 29, 2019

Welcome to Mr Excel forum

Try
Array formula in E1 copied down
=SUM(IF(FREQUENCY(IF(B$1:B$14=D1,MATCH(A$1:A$14,A$1:A$14,0)),ROW(A$1:A$14)-ROW(A$1)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

AndyPandy27 · Mar 29, 2019

Hi Kinno,

One other option is to use a PivotTable in conjunction with a helper column.

Add a helper column next to your data in Columns A & B, which concatenates the two:

Code:

=A1&" - "&B1

Then create a PivotTable of all three columns by going to Insert > PivotTable - before you click "OK" to create your PivotTable make sure to check the option to "Add this data to the Data Model" right at the bottom - this is important.

Now create a simple PivotTable with Column B values in the Rows section and Column C values in the Values section. Initially this will create a straight count of Column C values, but if you go into the Value Field Settings, scroll all the way to the bottom of the Summarize Values By tab, you should see "Distinct Count" at the bottom. Select that, click "OK" and you should now get the result you want.

Good luck,

AP

MickG · Mar 29, 2019

Another option:-
Your data starts row2, Results start "C1".

Code:

[COLOR="Navy"]Sub[/COLOR] MG29Mar40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
   
   [COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
            Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, -1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, -1).Value), Nothing
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
  
    ReDim ray(1 To Dic.Count, 1 To 2)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
       c = c + 1
        ray(c, 1) = k
        ray(c, 2) = Dic(k).Count
    [COLOR="Navy"]Next[/COLOR] k
Range("C1").Resize(c, 2) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Kinno · Apr 1, 2019

Hi all,

Thanks for all the responses! Lots of interesting approaches. I went with Marcelos approach for ease of use and my level of understanding! It worked which is great so thanks for that! I do however have a few further questions I hope you don't mind answering!

Firstly all my data is in the tables created with the Ctrl+T option. Would I be able to use the table column references in this formula and keep the formulas updating dynamically as more data is added to the table? If not I'll just update the formula accordingly each time I add new data to it!

Secondly is the formula dependant on being in E1? I tried doing it in E2 and adjusting it accordingly however it didnt seem to work. I'm not sure if that's the reason or if i simply mistyped the formula and couldnt see the typo.

Thanks again to everyone for the responses this is a great forum and I'm glad I took the leap of signing up!

Have a great day all

Marcelo Branco · Apr 1, 2019

Kinno said:
Firstly all my data is in the tables created with the Ctrl+T option. Would I be able to use the table column references in this formula and keep the formulas updating dynamically as more data is added to the table? If not I'll just update the formula accordingly each time I add new data to it!

Secondly is the formula dependant on being in E1? I tried doing it in E2 and adjusting it accordingly however it didnt seem to work. I'm not sure if that's the reason or if i simply mistyped the formula and couldnt see the typo.

Using a table (Table1) and inserting the first formula in E2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A

[/TD]
[TD="bgcolor: #DCE6F1"]

B

[/TD]
[TD="bgcolor: #DCE6F1"]

C

[/TD]
[TD="bgcolor: #DCE6F1"]

D

[/TD]
[TD="bgcolor: #DCE6F1"]

E

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1

[/TD]
[TD]

Patient

[/TD]
[TD]

Referral

[/TD]
[TD][/TD]
[TD]

Referral

[/TD]
[TD]

Result

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2

[/TD]
[TD]

Mario

[/TD]
[TD]

Insurer

[/TD]
[TD][/TD]
[TD]

Clinician

[/TD]
[TD]

3

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3

[/TD]
[TD]

Jane

[/TD]
[TD]

Clinician

[/TD]
[TD][/TD]
[TD]

Insurer

[/TD]
[TD]

1

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4

[/TD]
[TD]

Phil

[/TD]
[TD]

Website

[/TD]
[TD][/TD]
[TD]

GP

[/TD]
[TD]

4

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5

[/TD]
[TD]

Liam

[/TD]
[TD]

GP

[/TD]
[TD][/TD]
[TD]

Website

[/TD]
[TD]

2

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6

[/TD]
[TD]

David

[/TD]
[TD]

GP