Find number of empty rows between two cells in a column with given values

[rhivert]

Finding the position of first non-blank cell in a column or list between two cells in a column with given values

I hope someone could help me with the following issue:

I have a table in the following format:

Location.....Clinic Type

Ottawa......Ottawa Permanent
................Ottawa Mobiles
................Ottawa Bloodmobiles
Kingston.....Kingston Permanent
................Kingston Mobiles
Sudbury.....Sudbury Permanent
................Sudbury Mobiles

I am trying to create a dynamic range for the charts which are linked to the table above, which would find for example "Ottawa" and count the number of empty cells between "Ottawa and the following non-blank cell.. Which in this case would be Kingston.

This formula would then be inserted in an offset formula to create my dynamic range to pick the range of values corresponding the the respective value I am looking for in the table.

The problem is that the dimension of the table changes every week and the non-blank between two line items varies accordingly.

Also, I do not want to use any VBA because It is a dynamic ranges for a chart I am trying to come up with.

Basically to recap.. I need a formula that gives a count of the number of empty rows between two cells containing a certain value.....

Thanks in advance!

Randall

 

[rhivert]

Yes that is correct! Do youhave an alternative to the previous counta formula above?

 

[Aladin Akyurek]

Yes that is correct! Do youhave an alternative to the previous counta formula above?

Consider...

<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=296><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2417" width=68><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5603" width=158><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=68>Field-1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 118pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=158>Field-2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>Field-3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Ottawa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ottawa Permanent </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OT2500</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OT2501</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ottawa Mobiles</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OT2504</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OT2505</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ottawa Bloodmobile</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OT2506</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>OT2507</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Kingston</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Kingston Permanent</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>KI25500</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>KI25501</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Kingston Mobiles</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>KI25502</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Sudbury</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Sudbury Permanent</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>SU20500</TD></TR></TBODY></TABLE>

The data is assummed to be in A:C on Sheet1 (Adjust to suit.).
The data width is assumed to be fixed and set to 3 in what follows.

Define BigStr as referring to:

=REPT("z",255)

Define Srec as referring to:

=MIN(ROW(Sheet1!$A$2))

Define Lrec as referring to:

=MATCH(BigStr,Sheet1!$C:$C)

Define Cities as referring to:

=OFFSET(Sheet1!$A$2,0,0,Lrec-Srec+1)

Define StartOttawa as referring to:

=MATCH("Ottawa",Cities,0)

Define EndOttawa as referring to:

=IF(LOOKUP(BigStr,Cities)="Ottawa",Lrec-Srec+1,
   MIN(IF(Cities<>"",IF(ROW(Cities)-Srec+1 > 
    MATCH("Ottawa",Cities,0)+Srec-1,ROW(Cities)-Srec+1)))-1)

Create also separate size definitions for other cities.

Define OTTAWA as referring to:

=OFFSET(Sheet1!$A$2,StartOttawa-1,0,EndOttawa-StartOttawa+Srec-1,3)

Hope this helps.

 

[rhivert]

Thank you Aladin! I am working on the report today. I will try your method and let you know if I have any questions.

 

[rhivert]

Hi Aladin,

I tried your technic which seems to be picking a defined range, which is great, but correctly... I still need some tweaking and would apprcreciate if you could please explain the rationale for the names you defined above. That would shed some light on what I will need to change for it to give me the ranges needed.

 

[Aladin Akyurek]

Hi Aladin,

I tried your technic which seems to be picking a defined range, which is great, but correctly... I still need some tweaking and would apprcreciate if you could please explain the rationale for the names you defined above. That would shed some light on what I will need to change for it to give me the ranges needed.

a] I need BigStr as a look up value in a MATCH formula to datermine the row num of the last text value from an appropriate reference.

b] Lrec (the row num of the last record) is defined with [a]. Note that MATCH is applied to the most complete reference (column C).

c] Srec is simply the row number of the row at where the data area starts.

d] Cities is defined using OFFSET...

=OFFSET(Sheet1!$A$2,0,0,Lrec-Srec+1)

which means that Cities starts at A2 in column A and consists of

Lrec-Srec+1

items.

e] In order to define OTTAWA, we need where Ottawa data start and where it ends.

f] The following...

=MATCH("Ottawa",Cities,0)

yields the position of the first Ottawa record. Hence StartOttawa.

g] Determining the last Ottawa record (EndOttawa) is a liitle harder...

If the last record of Cities is equal to Ottawa, then the row number
for that record within Cities is:

Lrec-Srec+1

Otherwise: It's the smallest of the within Cities rows that is larger than the start Ottowa row, dtermined with:

MIN(IF(Cities<>"",IF(ROW(Cities)-Srec+1 >
MATCH("Ottawa",Cities,0)+Srec-1,ROW(Cities)-Srec+1)))-1)

h] Given [f] and [g], Ottawa is defined using an OFFSET expression:

=OFFSET(Sheet1!$A$2,StartOttawa-1,0,EndOttawa-StartOttawa+Srec-1,3)

where the width parameter is set here to 3.

Hope this helps.