How to count names repeated in consecutive rows as "1"

[halesowenmum]

Hi there

Can anyone assist with this?

I've got a spreadsheet with rows of patient names in it. In some cases it's one name per row and in others there may be more than one row with the same name in it. Each row signifies a clinical review having been conducted for that patient. It's one review max conducted per day. Where the patient was in for say three days there name would appear the same in three separate rows but always one above the other whilst the dates in the date column would be different to show the three different days they were reviewed on.

I need to count up the ones where the same name appears multiple times so that if there were three rows it would mean the patient had received three clinical reviews over three different consecutive days - BUT it would still only count as one patient). It's that count of patients that I need.

The above doesn't account for the following though and I'm not sure if we can cover this off somehow:

Patients may finish their visit and be discharged. But they might return to hospital a week or a month later if they get ill again. That would count effectively as 'another' patient (even though it's the same one). Is there any way to capture them as well??

My columns references are:

E - first name
F - last name
AE - date

Many thanks.

 

[MickG]

This is a Double Click Event based on a selection in column "E"
If the patient has had 2 appointments of 3 continuous days then later on 4 continuous days then the results in a msgbox will show
The patients name and = 3,4.
If you want to show all the patients results in sheet or single results please state where you would like to see them.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
    [COLOR="Navy"]If[/COLOR] Not Intersect(Target, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & " " & Dn.Offset(, 1).Value
      [COLOR="Navy"]If[/COLOR] Target.Value & " " & Target.Offset(, 1).Value = Txt [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
       [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng.Areas
    nStr = nStr & IIf(nStr = "", R.Count, "," & R.Count)
[COLOR="Navy"]Next[/COLOR] R
    MsgBox Txt & " = " & nStr
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[halesowenmum]

Oooh, I am sorry I don't understand (sorry!!). Is this VBL - I've no idea how I'd enter that and make it work?

 

[Snakehips]

Are you looking for something like below, probably the total patient see ions ?

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	First	Last	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	Date	*	*	*
2	Tom	Jones	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	01/01/2016	*	Total reviews	11
3	Tom	Jones	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	02/01/2016	*	Total Patients	6
4	Tom	Jones	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	03/01/2016	*	Total Patient Sessions	8
5	Fred	Flintstone	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	04/01/2016	*	*	*
6	Carol	King	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	05/01/2016	*	*	*
7	Carol	King	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	06/01/2016	*	*	*
8	Tom	Thumb	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	07/01/2016	*	*	*
9	Bill	Jones	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	08/01/2016	*	*	*
10	Tom	Jones	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	09/01/2016	*	*	*
11	Sid	Strange	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	10/01/2016	*	*	*
12	Tom	Thumb	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	11/01/2016	*	*	*
Sheet1

NB the last two formulas are array formulas so confirm with Ctrl + Shift + Enter and excel will add the curly braces.

 

[halesowenmum]

Yes absolutely that's how it looks you clever sausage. That is so clever!

I'll have a look at trying to do this tomorrow on me old spreadsheet and see if I can input that all correctly.

Thank you and I'll feed back as to how I get on.

 

[halesowenmum]

Okey dokey so I've inserted the formulas and the first one worked a treat.

But when I put the other two in they just sit there as text.

I tried Ctrl Ving and Ctrl+Shift-Entering with and without {} but to no avail. Cells are currently set to general so I then tried having set it to number - no dice.

 

[halesowenmum]

Also, sorry, shouldn't Total Pts be 7?

Thumb has to count as 2 patients because he's come in gone home then come in again??

 

[Snakehips]

Okey dokey so I've inserted the formulas and the first one worked a treat.

But when I put the other two in they just sit there as text.

I tried Ctrl Ving and Ctrl+Shift-Entering with and without {} but to no avail. Cells are currently set to general so I then tried having set it to number - no dice.

Formulas must start with '=' . You are likely copying the whole of the above, including the curly braces and posting it into cell. The '{' will then make that as text. If so then REMOVE the curly braces from each end of the text and then do Ctrl + Shift + Enter and the braces will be entered automatically.

As for the Total Patients... apologies but please totally disregard that formula with I should not have posted. (Silly sausage) You will see it refers to column A and was just something I played with when working out your Total Sessions formula.
There are 6 unique patient names and two patients have each had two sessions so 8 sessions.

Hope that sorts it

 

[halesowenmum]

I have absolutely definitely copied the formula without the curlies and with the = sign and pasted in both possible ways but it still just displays it at text (doesn't add the curly braces) .... or it just doesn't do anything. Could it be any of my Excel settings???

Thanks for the explanation re the patient numbers!

 

[Snakehips]

Then it sounds like the cell you are trying to enter the formula into is set to TEXT format?
Select the empty cell and make sure format is set to GENERAL then enter the formula.