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.

 

[halesowenmum]

Hi

Thanks for this.

So I decided to try the AH4 formula and made absolutely sure that the cell I'm working in has Number format applied to it - it has.

I've manually typed it in with the = sign but without the {} and hit CSE - it goes in but returns #ERROR!.

I tried the formula using the numbers 99 and 100 as you have used and by replacing 99 with 345 and 100 with 345 but with either version I still just get #ERROR!.

=SUMPRODUCT((E2:E345&F2:F345<>E1:E344+F1:F344)*(E2:E345+F2:F345<>""))

I also wasn't clear if it's two quote marks " at the end or four '. If I try the formula with four ' instead of two " it won't let me complete entering it so the above is 2 x Shift+2.


Then even if I look at the Total Patient Reviews this isn't the result I'm expecting to see on your sample table that you kindly produced - it should be 7 patients as 6 came in in one batch and then later one of them came back a second time making 7 total. Is that what the AH4 formula is set up to do?

 

[oldbrewer]

01/10/2016	fred	1
02/10/2016	fred	0
03/10/2016	fred	0			problem statement
02/10/2016	bill	1
03/10/2016	bill	0			how many times has each patient attended ?
02/10/2016	sam	1			consecutive days to be treated as one visit
03/10/2016	dave	1
04/10/2016	dave	0
05/10/2016	dave	0
05/10/2016	fred	1
06/10/2016	fred	0
07/10/2016	fred	0
06/10/2016	bill	1
					fred	2
					bill	2
					sam	1
					dave	1
					it seems to me that with just one helper column
					you can get your answers
					formula in C2	note C1 must always be 1
					=IF(B2<>B1,1,IF(AND(A2=A1+1,B2=B1),0,1))
					formula giving 2 against fred
					=SUMPRODUCT(($C$1:$C$13)*($B$1:$B$13=F15))

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>

 

[Snakehips]

There are 6 unique patient names and two patients have each had two sessions so 8 sessions.

Let's sort the expected result first.
Is the above not correct?

6 unique patients 4 of which had just 1 appointment or session and BOTH Tom Jones and Tom Thumb had two separate visits ??

For me that makes 8 !!

If I have this wrong then please explain in words an idiot can understand.

 

[halesowenmum]

Cast me out into the hinterlands for I was only reading the last names. I am an awful human being.

You are right of course, it's 8 - oh the shame.

 

[Snakehips]

Cast me out into the hinterlands for I was only reading the last names. I am an awful human being.

You are right of course, it's 8 - oh the shame.

Don't be too hard on yourself. Your not necessarily a total worthless waste of space, you're just flamin' crap with Excel !!!!

No idea at all why you are unable to enter the array formula.
Are you ok to access a sample file if I upload one to DropBox?

 

[oldbrewer]

the advantage of being a real beginner with excel is that you will improve much more than Snakehips will as he is on the asymptotic part of the curve....

 

[halesowenmum]

Thank you Oldbrewer!

If all I did all day was Excel (other than bog standard spreadsheets 99% of the time that require virtually no analysis) I would get better but for so many of us, we use it, we find we can't do what we want, we learn something new, then we forget it as we have many sleeps between then and the next time we use it, and thus we are undone. Having just turned 50 at the weekend I fear this memory loss can only get worse...

I also find that even if I go back and look at the thing I used the last time (note my proficient use of Excel terminology) I then can't get it to work for the similar yet slightly different scenario I see before on this new occasion - so I'm back to square one coming on here and harrassing all you clever lot.

It is what it is!

Now to respond to that Snakehips....

 

[halesowenmum]

Cheers bud!

I'll give it a go on the told Dropbox although I'll have to see if I can actually get on it - NHS computers/servers are always locked down really tight and Citrix based so I've certainly been some places where I couldn't even download Dropbox let alone get it to run.

 

[halesowenmum]

Can't get this to work sadly:

	A	B	C	D	E
1	FRANCES	1	#VALUE!	BACON	19.10.2016
2	DYLAN	1	#VALUE!	RABBIT	1.11.2016
3	DYLAN	1	#VALUE!	RABBIT	1.11.2016
4	JAYNE	1	#VALUE!	ANDDICK	31.10.2016
5	JAYNE	#VALUE!	#VALUE!	ANDDICK	31.10.2016
6	PERDITA	#VALUE!	#VALUE!	PERSIMMON	30.10.2016
7	PERDITA	#VALUE!	#VALUE!	PERSIMMON	30.10.2016
8	PERDITA	#VALUE!	#VALUE!	PERSIMMON	30.10.2016
		The =IF(B2 one	The =SUMPRODUCT one
	Formatted as:	Number	Number

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I don't know what you mean about Col 1 must always be 1? Which is Col 1 in your example?

However what I get is shown above.

 

[Snakehips]

@ halesowenmum

You have PM

No, scratch that. I have sent you a PM