Count of cell blocks

[hawk77]

Hi all,

I am trying to solve a pattern problem and need your help desperately. Is doing my head in.

So I have a number of clients that have booked an appointment. As you can see in the file the weeks are at the top. What I want to find out for each customer is:
1. count the number of appointment blocks, where an appointment block is sequence of 1 or more appointments one after the other and with no more than 4
weeks off. Say for instance a person will have an appointment in week 5 and 6 then week 9 they have another appointment. The 5, 6, and 9 would count as a block. If the appointment is on week 11 then only 5 and 6 will be counted as a block and appointment in week 11 as another block. I hope it makes sense. I tried using =CEILING(COUNT(B3:DE3,"<>")/5,1)...but the result is wrong. For example C7:U7 can be counted as one block as the gaps don't go longer than four weeks (weeks count is at the top)! 2. Average number of appointments within a block
3. Average time difference between the blocks. Sometimes it might be longer than 4 weeks...it could be 6 months. I need this number in weeks.


All this will be on rolling data of 12 months.


Data looks like this :
Week number at the top
Name of customer on the side

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15......
Jan 1 2 1 1 1 1 1
Ben 1 1 1 1 1
Jony 1 1 1 1 1


Jan 2 appointment blocks
Ben 2 app blocks
Jony 3 app blocks


Average appointment within a block
Jan 2.6
Ben 2
Jony 1.5

etc

 

[Peter_SSs]

Welcome to the MrExcel board!

Some of your data & requirements are not clear to me.

1. All your appointments except one are denoted by a "1". Is the "2" for Jan a mistake, or does it mean something different? For the moment I have assumed it is a mistake. Please clarify.

2. In relation to the "average appointments per block" you give the result for Jan as 2.6. Since she had 2 appointments blocks and averaged 2.6 appointments per block that would give a total of 5.2 appointments which doesn't make sense to me either in a logical sense (how can you have 0.2 of an appointment) or from the sample data. Can you please clarify.

3. You also wanted "Average time difference between the blocks." For the sample data below, what would be the expected results & why? I'm particularly interested in what you would want returned for Tim, Tom and Joe, but please confirm the other results that I have manually entered in column V or advise what should be different and why.

For the count of blocks, here are two user-defined functions that you might consider. One uses Regular Expressions and one uses a looping method. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note that if you want to use a different number of weeks between, say 3 instead of the default 4, then you could use the functions with a second argument like
=NumBlocks(B2:P2,3)

Function NumBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = " {" & MinBlockSplit + 1 & ",}(?=1)"
  NumBlocks = RX.Execute(String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))).Count
End Function


Function CountBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Dim i As Long
  Dim s As String
  
  s = String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))
  For i = 1 To Len(s) - MinBlockSplit - 1
    If Mid(s, i, MinBlockSplit + 2) = String(MinBlockSplit + 1, " ") & 1 Then CountBlocks = CountBlocks + 1
  Next i
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15						Av Between
2	Jan	1		1	1					1	1	1		1					2	2		4
3	Ben				1	1	1	1						1					2	2		5
4	Jony		1						1	1					1	1			3	3		4.5
5	Tim	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1			1	1		?
6	Tom																		0	0		?
7	Ted	1														1			2	2		13
8	Joe											1							1	1		?
Count Blocks

 

[Peter_SSs]

Here is another option using standard worksheet functions that I think also think does what you want. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	Jan	1		1	1					1	1	1		1				2
3	Ben				1	1	1	1						1				2
4	Jony		1						1	1					1	1		3
5	Tim	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1		1
6	Tom																	0
7	Ted	1														1		2
8	Joe											1						1
Count Blocks (2)

 

[hawk77]

Hi Peter this is brilliant, thanks a lot for your assistance with this.
Please see my answers below:

1. All your appointments except one are denoted by a "1". Is the "2" for Jan a mistake, or does it mean something different? For the moment I have assumed it is a mistake. Please clarify.
The numbers at the top represent the week number in the year. Yes, there are cases where the customer might actually have two appointments in the same week. Very rare occurrence however!

2. In relation to the "average appointments per block" you give the result for Jan as 2.6. Since she had 2 appointments blocks and averaged 2.6 appointments per block that would give a total of 5.2 appointments which doesn't make sense to me either in a logical sense (how can you have 0.2 of an appointment) or from the sample data. Can you please clarify.
Sure thing Peter! Jan should have been 3.5 rather than 2.5. So, say for instance someone had 5 appointments block then another block of 3, then she starts another one. We will then know that at the 3rd appointment we will need to make a contact with the customer to remind them about the rest of the appointment. (i.e you're half way through, blah blah). I would like to have the average number rounded down, as it would make us a bit more proactive.

3. You also wanted "Average time difference between the blocks." For the sample data below, what would be the expected results & why? I'm particularly interested in what you would want returned for Tim, Tom and Joe, but please confirm the other results that I have manually entered in column V or advise what should be different and why.

Tom and Joe should have 0, which together with the number of appointment blocks (in their case is 1) should show that they need re engaging. Tim, a bit different. The data is actually spread accross two years. so if he has made no contact within that period, then he will get 0. Otherwise, whatever the figure is. Everything will be calculated together.

Function NumBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = " {" & MinBlockSplit + 1 & ",}(?=1)"
  NumBlocks = RX.Execute(String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))).Count
End Function


Function CountBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Dim i As Long
  Dim s As String
  
  s = String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))
  For i = 1 To Len(s) - MinBlockSplit - 1
    If Mid(s, i, MinBlockSplit + 2) = String(MinBlockSplit + 1, " ") & 1 Then CountBlocks = CountBlocks + 1
  Next i
End Function

This has worked great for the number of appointment block. Still struggling with the average figures. What is the difference between the count block and numb blocks as it looks like it gives me the same outcome?

Thanks a lot again for your help with this mate.

 

[Peter_SSs]

Hi Peter this is brilliant, thanks a lot for your assistance with this.
Please see my answers below:

1.Yes, there are cases where the customer might actually have two appointments in the same week. Very rare occurrence however!

OK, understand that now. My codes need a slight tweak to account for that - see below.

2.Sure thing Peter! Jan should have been 3.5 rather than 2.5.
I would like to have the average number rounded down, ..

First part doesn't make sense to me. Given your answer to 1 above, Jan had 4 appointments in the first block (1+2+1) and 4 appointments in the second block (1+1+1+1). To me then, the average number of appointments per block is 4. How do you get 3.5?
For the secound part, rounded down to what? (eg 2 decimal places or whole number or ...)

3. ... please confirm the other results that I have manually entered in column V or advise what should be different and why.

Tom and Joe should have 0, which together with the number of appointment blocks (in their case is 1) should show that they need re engaging. Tim, a bit different. The data is actually spread accross two years. so if he has made no contact within that period, then he will get 0. Otherwise, whatever the figure is. Everything will be calculated together.

You didn't address the part of my request shown above. I also didn't really understand what you want for Tim. Since he has only 1 block there is no time "between blocks". So, does he get a 0 or something else?

What is the difference between the count block and numb blocks as it looks like it gives me the same outcome?

They do/should give the same outcome, I was just offering you a choice.
Some people would rather avoid Regular Expressions like in NumBlocks and some people don't like a lot of looping like in CountBlocks.

Revised codes for the two UDFs

Function NumBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = " {" & MinBlockSplit + 1 & ",}(?=\d)"
  NumBlocks = RX.Execute(String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))).Count
End Function


Function CountBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Dim i As Long
  Dim s As String
  
  s = String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))
  For i = 1 To Len(s) - MinBlockSplit - 1
    If Mid(s, i, MinBlockSplit + 2) Like String(MinBlockSplit + 1, " ") & "#" Then CountBlocks = CountBlocks + 1
  Next i
End Function

.. and a suggestion for Average per Block (no rounding yet pending clarification)
I'm waiting on clarification of point 3 before considering Average Between Blocks.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15			NumBlocks	CountBlocks	Average per Block	Av Between
2	Jan	1		2	1					1	1	1		1					2	2	4	4
3	Ben				1	1	1	1						1					2	2	2.5	5
4	Jony		1						1	1					1	1			3	3	1.666666667	4.5
5	Tim	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1			1	1	15	?
6	Tom																		0	0	#N/A	?
7	Ted	1														1			2	2	1	13
8	Joe											1							1	1	1	?
Count Blocks (2)

 

[hawk77]

OK, understand that now. My codes need a slight tweak to account for that - see below.

First part doesn't make sense to me. Given your answer to 1 above, Jan had 4 appointments in the first block (1+2+1) and 4 appointments in the second block (1+1+1+1). To me then, the average number of appointments per block is 4. How do you get 3.5?
For the secound part, rounded down to what? (eg 2 decimal places or whole number or ...)


You didn't address the part of my request shown above. I also didn't really understand what you want for Tim. Since he has only 1 block there is no time "between blocks". So, does he get a 0 or something else?


They do/should give the same outcome, I was just offering you a choice.
Some people would rather avoid Regular Expressions like in NumBlocks and some people don't like a lot of looping like in CountBlocks.

Revised codes for the two UDFs

Function NumBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = " {" & MinBlockSplit + 1 & ",}(?=\d)"
  NumBlocks = RX.Execute(String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))).Count
End Function


Function CountBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Dim i As Long
  Dim s As String
  
  s = String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))
  For i = 1 To Len(s) - MinBlockSplit - 1
    If Mid(s, i, MinBlockSplit + 2) Like String(MinBlockSplit + 1, " ") & "#" Then CountBlocks = CountBlocks + 1
  Next i
End Function

.. and a suggestion for Average per Block (no rounding yet pending clarification)
I'm waiting on clarification of point 3 before considering Average Between Blocks.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15			NumBlocks	CountBlocks	Average per Block	Av Between
2	Jan	1		2	1					1	1	1		1					2	2	4	4
3	Ben				1	1	1	1						1					2	2	2.5	5
4	Jony		1						1	1					1	1			3	3	1.666666667	4.5
5	Tim	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1			1	1	15	?
6	Tom																		0	0	#N/A	?
7	Ted	1														1			2	2	1	13
8	Joe											1							1	1	1	?
Count Blocks (2)

Ok so with Jan initially she had 4 and 4 which indeed is an average of 4. Then the table below we changed it to 3 and 4 which made it 3.5 as an average. My mistake, I apologize . As for rounding is down to whole number. So if it is 3.7, rounded to 3 rather than 4.

Tom fella should have 0 because in the above period he's made no other block of appointments, so he's only got one.

 

[hawk77]

Also, your average times in between are correct.

 

[Peter_SSs]

Ok so with Jan initially she had 4 and 4 which indeed is an average of 4. Then the table below we changed it to 3 and 4 which made it 3.5 as an average. My mistake, ..

Ah, not your mistake. I just didn't realise that you were using my sample data where I had changed that 2 to a 1.
Now I understand.

Also, your average times in between are correct.

OK, I'm assuming the use of the regular expression UDF NumBlocks() from post 5 (& repeated here) to count the number of blocks. Here then are my suggestions for each of the 3 things you want calculated.

Function NumBlocks(r As Range, Optional MinBlockSplit As Long = 4) As Long
  Static RX As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = " {" & MinBlockSplit + 1 & ",}(?=\d)"
  NumBlocks = RX.Execute(String(MinBlockSplit + 1, " ") & Join(Application.Index(r.Value, 1, 0))).Count
End Function

Function AvBetween(rng As Range, Optional MinBlockGap As Long = 4) As Double
  Static RX As Object
  Dim m As Object
  Dim Tot As Long, i As Long
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = " {" & MinBlockGap + 1 & ",}(?=\d)"
  Set m = RX.Execute(String(MinBlockGap + 1, " ") & Join(Application.Index(rng.Value, 1, 0)))
  For i = 2 To m.Count
    Tot = Tot + m(i - 1).Length - 1
  Next i
  If Tot > 0 Then AvBetween = Tot / (NumBlocks(rng, MinBlockGap) - 1)
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15			NumBlocks	Average per Block	Av Between
2	Jan	1		2	1					1	1	1		1					2	4	4
3	Ben				1	1	1	1						1					2	2	5
4	Jony		1						1	1					1	1			3	1	4.5
5	Tim	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1			1	15	0
6	Tom																		0	#N/A	0
7	Ted	1														1			2	1	13
8	Joe											1							1	1	0
Count Blocks (2)

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.