Count first instance of two unique entries

[Kinno]

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

A	B
Mario	Insurer
Jane	Clinician
Phil	Website
Liam	GP
David	GP
Alexi	Clinician
Rob	Website
Ana	Clinician
David	GP
William	GP
Jane	Clinician
Megan	GP
Phil	Website
Phil	Website

<tbody>
</tbody>

D	E
Clinician	?
Insurer	?
Website	?
GP	?

<tbody>
</tbody>

So hopefully someone can help me out with this

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

Thanks

 

[Marcelo Branco]

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]

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:

=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]

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

[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]

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]

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

	A​	B​	C​	D​	E​
1​	Patient​	Referral​		Referral​	Result​
2​	Mario​	Insurer​		Clinician​	3​
3​	Jane​	Clinician​		Insurer​	1​
4​	Phil​	Website​		GP​	4​
5​	Liam​	GP​		Website​	2​
6​	David​	GP​
7​	Alexi​	Clinician​
8​	Rob​	Website​
9​	Ana​	Clinician​
10​	David​	GP​
11​	William​	GP​
12​	Jane​	Clinician​
13​	Megan​	GP​
14​	Phil​	Website​
15​	Phil​	Website​
16​

<tbody>
</tbody>

Array formula in E2 copied down
=SUM(IF(FREQUENCY(IF(Table1[Referral]=D2,MATCH(Table1[Patient],Table1[Patient],0)),ROW(Table1[Patient])-MIN(ROW(Table1[Patient]))+1),1))
Ctrl+Shift+Enter

M.

 

[Kinno]

Thank you Marcelo!

Thats exactly what i wanted to hear! Have a great day!

 

[Marcelo Branco]

You're welcome. Glad to help.

M.