# Count Intervals of Filtered TEXT values in Column and Return Count across a Row

1. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Arthur,

Thanks for sample file - great!

Cheers,
Sam

Sam wrote:
>Hi Art,
>
>Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk
>
>Thanks
>Sam
>
>>Hi Sam,
>>

>[quoted text clipped - 34 lines]
>>> Thanks
>>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

2. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Sam,

Domenic has given you a fine answer using formulas as you asked. I looked at
it from a macro standpoint and if you're interested in that version send me

CHORDially,
Art Farrell

"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance

and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row - starting with the most recent ( the

LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all

in
> column "B."
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from

the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract - Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB - is

a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate

the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet - using a separate column for each TEXT

value
> instance - interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT

value
> - this should be the basis for the FIRST calculated interval returned to

the
> New Sheet column B, Row380 should be used for the SECOND calculated

interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results - New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI

ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam
>
>
> --
> Message posted via http://www.officekb.com

3. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

>You'll get a #REF! error if there's no data in your range that meets the
>criterion or if there's only one cell that meets the criterion, and that
>cell is the first one, B19. Is this the case?

No to the above. My mistake as usual.

The reason I'm getting the #REF! error on all rows but the first: I
carelessly omitted part of the sentence in the last paragraph of my original
explanation. In full it should have read:

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
(Sample Data extract Sheet1).
So, on the Source Data sheet to find text value 50-51 need to look in column
B, to find text value 55-56 need to look in column C, to find text value 59-
60 need to look in column D etc. Each text value range is in a different
column on the Source Data Sheet.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
referencing is absoluted based on the information in my original Post. Do I
need to create separate Named Formulas for each individual text value range
to have the calculated intervals returned.

If you can offer any further suggestions - most appreciated.

Apologies for confusion.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

4. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Change/add the defined references, as follows (Insert > Name > Define):

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Name: FirstRow

Refers to:
=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
)

Adjust the range B19:D19 accordingly. Also, you can replace the array
constant {...} with a reference to a range of cells. So, for example,
if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
respectively, the MATCH part could be changed to...

MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)

You can use any other cells you want, but don't use row 18 on Sheet1
since that would affect the calculations.

Name: NumCount

Refers to:
=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)

Name: Range

Refers to:
=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
},0))

With regards to the range and constant array, the same thing applies
here as in the reference for FirstRow.

Click Ok

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> >You'll get a #REF! error if there's no data in your range that meets the
> >criterion or if there's only one cell that meets the criterion, and that
> >cell is the first one, B19. Is this the case?

>
> No to the above. My mistake as usual.
>
> The reason I'm getting the #REF! error on all rows but the first: I
> carelessly omitted part of the sentence in the last paragraph of my original
> explanation. In full it should have read:
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
> (Sample Data extract Sheet1).
> So, on the Source Data sheet to find text value 50-51 need to look in column
> B, to find text value 55-56 need to look in column C, to find text value 59-
> 60 need to look in column D etc. Each text value range is in a different
> column on the Source Data Sheet.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
> referencing is absoluted based on the information in my original Post. Do I
> need to create separate Named Formulas for each individual text value range
> to have the calculated intervals returned.
>
> If you can offer any further suggestions - most appreciated.
>
> Apologies for confusion.
>
> Thanks
> Sam

5. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Name: Range
> Refers to:
> =Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
> )
> Click Add - My data actually starts in Row no.19 but there are text labels in
> Row no.18.

Yes, I understand that your data starts in row 19. The reference starts
at row 18, and that's by design. I should have made note of it so that
there wouldn't be any confusion.

> I'm not sure what I may have done incorrectly? I do get the correct results
> for the first row on the NewSheet.

You'll get a #REF! error if there's no data in your range that meets the
criterion or if there's only one cell that meets the criterion, and that
cell is the first one, B19. Is this the case?

6. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Your suggested amendments to accommodate my scenario works perfect!

Thank you very much for your brilliant assistance and your time - very much
appreciated.

Cheers
Sam

Domenic wrote:
>Change/add the defined references, as follows (Insert > Name > Define):
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>
>Name: FirstRow
>
>Refers to:
>=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
>)
>
>Adjust the range B19:D19 accordingly. Also, you can replace the array
>constant {...} with a reference to a range of cells. So, for example,
>if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
>respectively, the MATCH part could be changed to...
>
>MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)
>
>You can use any other cells you want, but don't use row 18 on Sheet1
>since that would affect the calculations.
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)
>
>
>Name: Range
>
>Refers to:
>=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
>},0))
>
>With regards to the range and constant array, the same thing applies
>here as in the reference for FirstRow.
>
>Click Ok
>
>Hope this helps!
>
>> Hi Domenic,
>>

>[quoted text clipped - 39 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

7. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Art,

Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk

Thanks
Sam

Art Farrell wrote:
>Hi Sam,
>
>Domenic has given you a fine answer using formulas as you asked. I looked at
>it from a macro standpoint and if you're interested in that version send me
>
>CHORDially,
>Art Farrell
>
>> Hi All,
>>
>> I require a Formula to calculate the intervals between each individual
>> occurrence of a TEXT value (the number of Rows between the LAST instance and
>> the PREVIOUS instance) in a column, and return each interval result to a
>> separate Column on the same Row - starting with the most recent ( the

>LAST)
>> occurrence.
>>

>[quoted text clipped - 12 lines]
>> ColC Houses the Text values 55-56
>> Where I have written the word blank (purely as a visual aid) in ColB - is

>a
>> Formula that has returned empty text.
>>

>[quoted text clipped - 22 lines]
>> Row numbers as explained above. The results should be returned in a
>> horizontal array to a new Sheet - using a separate column for each TEXT

>value
>> instance - interval calculation.
>>
>> In the above example, Row397 is the LAST Row with an instance of a TEXT value
>> - this should be the basis for the FIRST calculated interval returned to

>the
>> New Sheet column B, Row380 should be used for the SECOND calculated interval
>> returned to column C etc.

>[quoted text clipped - 29 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

8. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Thank you for all your help.

Your Formula does provide the expected results for the first Row - Row8 on
the Sheet I refer to as NewSheet (for the expected results). However, I get
the REF Error message on all subsequent rows.

I may have confused the sheet names in the various formulas, so, I'll just
run through what I've done:

1) Select cell B8 = NewSheet - expected results

2) Define the following references (Insert > Name > Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add - Defined as stated above

Name: Range
Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
)
Click Add - My data actually starts in Row no.19 but there are text labels in
Row no.18.

Name: NumCount
Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
Click Ok - I've taken Sheet1 in the COUNTIF Range to mean my NewSheet with
the expected results.
And this (Sheet1!\$B\$19<>Sheet1!\$A8) - to mean (SourceDataSheet!\$B
\$19<>NewSheet!\$A8)

Name: Array1
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
Click Add - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

Name: Array2
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
Click Ok - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8 - completed as suggested.

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:
=TRANSPOSE(Array2-Array1)-1

I'm not sure what I may have done incorrectly? I do get the correct results
for the first row on the NewSheet.

Any further help very much appreciated.

Thanks
Sam

Domenic wrote:
>Try the following...
>
>1) Select cell B8
>
>2) Define the following references (Insert > Name > Define):
>
>Name: BigText
>
>Refers to: =REPT("z",255)
>
>
>Name: Range
>
>Refers to:
>=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
>5536))
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
>
>Click Ok
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>Click Ok
>
>3) Select/highlight the range of cells in row 8 that will contain the
>results, for example, B8:AE8
>
>4) With the range of cells selected/highlighted, enter the following
>formula and confirm with CONTROL+SHIFT+ENTER:
>
>=TRANSPOSE(Array2-Array1)-1
>
>5) Copy the formula to other rows. To do so, select B8:AE8 first, then
>Copy/Paste.
>
>6) Use conditional formatting to hide the error values that will occur:
>
>a) Select B8
>
>b) Format > Conditional Formatting > Formula Is
>
>c) Enter the following formula:
>
>=ISERROR(B8)
>
>d) Choose 'White' as your font
>
>e) Click Ok
>
>f) Copy the format to other cells using the 'Format Painter' or 'Copy >
>Paste Special > Formats'.
>
>Hope this helps!
>
>> Hi All,
>>

>[quoted text clipped - 81 lines]
>> Thanks
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

9. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Try the following...

1) Select cell B8

2) Define the following references (Insert > Name > Define):

Name: BigText

Refers to: =REPT("z",255)

Name: Range

Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
5536))

Name: NumCount

Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)

Click Ok

Name: Array1

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Ok

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:

=TRANSPOSE(Array2-Array1)-1

5) Copy the formula to other rows. To do so, select B8:AE8 first, then
Copy/Paste.

6) Use conditional formatting to hide the error values that will occur:

a) Select B8

b) Format > Conditional Formatting > Formula Is

c) Enter the following formula:

=ISERROR(B8)

d) Choose 'White' as your font

e) Click Ok

f) Copy the format to other cells using the 'Format Painter' or 'Copy >
Paste Special > Formats'.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row – starting with the most recent ( the LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all in
> column “B.”
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract – Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB – is a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet – using a separate column for each TEXT value
> instance – interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT value
> – this should be the basis for the FIRST calculated interval returned to the
> New Sheet column B, Row380 should be used for the SECOND calculated interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam

10. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Thank you for all your help.

Your Formula does provide the expected results for the first Row - Row8 on
the Sheet I refer to as NewSheet (for the expected results). However, I get
the REF Error message on all subsequent rows.

I may have confused the sheet names in the various formulas, so, I'll just
run through what I've done:

1) Select cell B8 = NewSheet - expected results

2) Define the following references (Insert > Name > Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add - Defined as stated above

Name: Range
Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
)
Click Add - My data actually starts in Row no.19 but there are text labels in
Row no.18.

Name: NumCount
Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
Click Ok - I've taken Sheet1 in the COUNTIF Range to mean my NewSheet with
the expected results.
And this (Sheet1!\$B\$19<>Sheet1!\$A8) - to mean (SourceDataSheet!\$B
\$19<>NewSheet!\$A8)

Name: Array1
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
Click Add - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

Name: Array2
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
Click Ok - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8 - completed as suggested.

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:
=TRANSPOSE(Array2-Array1)-1

I'm not sure what I may have done incorrectly? I do get the correct results
for the first row on the NewSheet.

Any further help very much appreciated.

Thanks
Sam

Domenic wrote:
>Try the following...
>
>1) Select cell B8
>
>2) Define the following references (Insert > Name > Define):
>
>Name: BigText
>
>Refers to: =REPT("z",255)
>
>
>Name: Range
>
>Refers to:
>=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
>5536))
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
>
>Click Ok
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>Click Ok
>
>3) Select/highlight the range of cells in row 8 that will contain the
>results, for example, B8:AE8
>
>4) With the range of cells selected/highlighted, enter the following
>formula and confirm with CONTROL+SHIFT+ENTER:
>
>=TRANSPOSE(Array2-Array1)-1
>
>5) Copy the formula to other rows. To do so, select B8:AE8 first, then
>Copy/Paste.
>
>6) Use conditional formatting to hide the error values that will occur:
>
>a) Select B8
>
>b) Format > Conditional Formatting > Formula Is
>
>c) Enter the following formula:
>
>=ISERROR(B8)
>
>d) Choose 'White' as your font
>
>e) Click Ok
>
>f) Copy the format to other cells using the 'Format Painter' or 'Copy >
>Paste Special > Formats'.
>
>Hope this helps!
>
>> Hi All,
>>

>[quoted text clipped - 81 lines]
>> Thanks
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

11. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Art,

Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk

Thanks
Sam

Art Farrell wrote:
>Hi Sam,
>
>Domenic has given you a fine answer using formulas as you asked. I looked at
>it from a macro standpoint and if you're interested in that version send me
>
>CHORDially,
>Art Farrell
>
>> Hi All,
>>
>> I require a Formula to calculate the intervals between each individual
>> occurrence of a TEXT value (the number of Rows between the LAST instance and
>> the PREVIOUS instance) in a column, and return each interval result to a
>> separate Column on the same Row - starting with the most recent ( the

>LAST)
>> occurrence.
>>

>[quoted text clipped - 12 lines]
>> ColC Houses the Text values 55-56
>> Where I have written the word blank (purely as a visual aid) in ColB - is

>a
>> Formula that has returned empty text.
>>

>[quoted text clipped - 22 lines]
>> Row numbers as explained above. The results should be returned in a
>> horizontal array to a new Sheet - using a separate column for each TEXT

>value
>> instance - interval calculation.
>>
>> In the above example, Row397 is the LAST Row with an instance of a TEXT value
>> - this should be the basis for the FIRST calculated interval returned to

>the
>> New Sheet column B, Row380 should be used for the SECOND calculated interval
>> returned to column C etc.

>[quoted text clipped - 29 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

12. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Try the following...

1) Select cell B8

2) Define the following references (Insert > Name > Define):

Name: BigText

Refers to: =REPT("z",255)

Name: Range

Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
5536))

Name: NumCount

Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)

Click Ok

Name: Array1

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Ok

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:

=TRANSPOSE(Array2-Array1)-1

5) Copy the formula to other rows. To do so, select B8:AE8 first, then
Copy/Paste.

6) Use conditional formatting to hide the error values that will occur:

a) Select B8

b) Format > Conditional Formatting > Formula Is

c) Enter the following formula:

=ISERROR(B8)

d) Choose 'White' as your font

e) Click Ok

f) Copy the format to other cells using the 'Format Painter' or 'Copy >
Paste Special > Formats'.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row – starting with the most recent ( the LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all in
> column “B.”
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract – Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB – is a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet – using a separate column for each TEXT value
> instance – interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT value
> – this should be the basis for the FIRST calculated interval returned to the
> New Sheet column B, Row380 should be used for the SECOND calculated interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam

13. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Your suggested amendments to accommodate my scenario works perfect!

Thank you very much for your brilliant assistance and your time - very much
appreciated.

Cheers
Sam

Domenic wrote:
>Change/add the defined references, as follows (Insert > Name > Define):
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>
>Name: FirstRow
>
>Refers to:
>=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
>)
>
>Adjust the range B19:D19 accordingly. Also, you can replace the array
>constant {...} with a reference to a range of cells. So, for example,
>if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
>respectively, the MATCH part could be changed to...
>
>MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)
>
>You can use any other cells you want, but don't use row 18 on Sheet1
>since that would affect the calculations.
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)
>
>
>Name: Range
>
>Refers to:
>=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
>},0))
>
>With regards to the range and constant array, the same thing applies
>here as in the reference for FirstRow.
>
>Click Ok
>
>Hope this helps!
>
>> Hi Domenic,
>>

>[quoted text clipped - 39 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

14. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Name: Range
> Refers to:
> =Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
> )
> Click Add - My data actually starts in Row no.19 but there are text labels in
> Row no.18.

Yes, I understand that your data starts in row 19. The reference starts
at row 18, and that's by design. I should have made note of it so that
there wouldn't be any confusion.

> I'm not sure what I may have done incorrectly? I do get the correct results
> for the first row on the NewSheet.

You'll get a #REF! error if there's no data in your range that meets the
criterion or if there's only one cell that meets the criterion, and that
cell is the first one, B19. Is this the case?

15. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Arthur,

Thanks for sample file - great!

Cheers,
Sam

Sam wrote:
>Hi Art,
>
>Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk
>
>Thanks
>Sam
>
>>Hi Sam,
>>

>[quoted text clipped - 34 lines]
>>> Thanks
>>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

16. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Change/add the defined references, as follows (Insert > Name > Define):

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Name: FirstRow

Refers to:
=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
)

Adjust the range B19:D19 accordingly. Also, you can replace the array
constant {...} with a reference to a range of cells. So, for example,
if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
respectively, the MATCH part could be changed to...

MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)

You can use any other cells you want, but don't use row 18 on Sheet1
since that would affect the calculations.

Name: NumCount

Refers to:
=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)

Name: Range

Refers to:
=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
},0))

With regards to the range and constant array, the same thing applies
here as in the reference for FirstRow.

Click Ok

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> >You'll get a #REF! error if there's no data in your range that meets the
> >criterion or if there's only one cell that meets the criterion, and that
> >cell is the first one, B19. Is this the case?

>
> No to the above. My mistake as usual.
>
> The reason I'm getting the #REF! error on all rows but the first: I
> carelessly omitted part of the sentence in the last paragraph of my original
> explanation. In full it should have read:
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
> (Sample Data extract Sheet1).
> So, on the Source Data sheet to find text value 50-51 need to look in column
> B, to find text value 55-56 need to look in column C, to find text value 59-
> 60 need to look in column D etc. Each text value range is in a different
> column on the Source Data Sheet.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
> referencing is absoluted based on the information in my original Post. Do I
> need to create separate Named Formulas for each individual text value range
> to have the calculated intervals returned.
>
> If you can offer any further suggestions - most appreciated.
>
> Apologies for confusion.
>
> Thanks
> Sam

17. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

>You'll get a #REF! error if there's no data in your range that meets the
>criterion or if there's only one cell that meets the criterion, and that
>cell is the first one, B19. Is this the case?

No to the above. My mistake as usual.

The reason I'm getting the #REF! error on all rows but the first: I
carelessly omitted part of the sentence in the last paragraph of my original
explanation. In full it should have read:

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
(Sample Data extract Sheet1).
So, on the Source Data sheet to find text value 50-51 need to look in column
B, to find text value 55-56 need to look in column C, to find text value 59-
60 need to look in column D etc. Each text value range is in a different
column on the Source Data Sheet.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
referencing is absoluted based on the information in my original Post. Do I
need to create separate Named Formulas for each individual text value range
to have the calculated intervals returned.

If you can offer any further suggestions - most appreciated.

Apologies for confusion.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

18. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Sam,

Domenic has given you a fine answer using formulas as you asked. I looked at
it from a macro standpoint and if you're interested in that version send me

CHORDially,
Art Farrell

"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance

and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row - starting with the most recent ( the

LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all

in
> column "B."
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from

the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract - Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB - is

a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate

the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet - using a separate column for each TEXT

value
> instance - interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT

value
> - this should be the basis for the FIRST calculated interval returned to

the
> New Sheet column B, Row380 should be used for the SECOND calculated

interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results - New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI

ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam
>
>
> --
> Message posted via http://www.officekb.com

19. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Art,

Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk

Thanks
Sam

Art Farrell wrote:
>Hi Sam,
>
>Domenic has given you a fine answer using formulas as you asked. I looked at
>it from a macro standpoint and if you're interested in that version send me
>
>CHORDially,
>Art Farrell
>
>> Hi All,
>>
>> I require a Formula to calculate the intervals between each individual
>> occurrence of a TEXT value (the number of Rows between the LAST instance and
>> the PREVIOUS instance) in a column, and return each interval result to a
>> separate Column on the same Row - starting with the most recent ( the

>LAST)
>> occurrence.
>>

>[quoted text clipped - 12 lines]
>> ColC Houses the Text values 55-56
>> Where I have written the word blank (purely as a visual aid) in ColB - is

>a
>> Formula that has returned empty text.
>>

>[quoted text clipped - 22 lines]
>> Row numbers as explained above. The results should be returned in a
>> horizontal array to a new Sheet - using a separate column for each TEXT

>value
>> instance - interval calculation.
>>
>> In the above example, Row397 is the LAST Row with an instance of a TEXT value
>> - this should be the basis for the FIRST calculated interval returned to

>the
>> New Sheet column B, Row380 should be used for the SECOND calculated interval
>> returned to column C etc.

>[quoted text clipped - 29 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

20. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Your suggested amendments to accommodate my scenario works perfect!

Thank you very much for your brilliant assistance and your time - very much
appreciated.

Cheers
Sam

Domenic wrote:
>Change/add the defined references, as follows (Insert > Name > Define):
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>
>Name: FirstRow
>
>Refers to:
>=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
>)
>
>Adjust the range B19:D19 accordingly. Also, you can replace the array
>constant {...} with a reference to a range of cells. So, for example,
>if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
>respectively, the MATCH part could be changed to...
>
>MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)
>
>You can use any other cells you want, but don't use row 18 on Sheet1
>since that would affect the calculations.
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)
>
>
>Name: Range
>
>Refers to:
>=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
>},0))
>
>With regards to the range and constant array, the same thing applies
>here as in the reference for FirstRow.
>
>Click Ok
>
>Hope this helps!
>
>> Hi Domenic,
>>

>[quoted text clipped - 39 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

21. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Arthur,

Thanks for sample file - great!

Cheers,
Sam

Sam wrote:
>Hi Art,
>
>Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk
>
>Thanks
>Sam
>
>>Hi Sam,
>>

>[quoted text clipped - 34 lines]
>>> Thanks
>>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

22. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Change/add the defined references, as follows (Insert > Name > Define):

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Name: FirstRow

Refers to:
=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
)

Adjust the range B19:D19 accordingly. Also, you can replace the array
constant {...} with a reference to a range of cells. So, for example,
if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
respectively, the MATCH part could be changed to...

MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)

You can use any other cells you want, but don't use row 18 on Sheet1
since that would affect the calculations.

Name: NumCount

Refers to:
=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)

Name: Range

Refers to:
=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
},0))

With regards to the range and constant array, the same thing applies
here as in the reference for FirstRow.

Click Ok

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> >You'll get a #REF! error if there's no data in your range that meets the
> >criterion or if there's only one cell that meets the criterion, and that
> >cell is the first one, B19. Is this the case?

>
> No to the above. My mistake as usual.
>
> The reason I'm getting the #REF! error on all rows but the first: I
> carelessly omitted part of the sentence in the last paragraph of my original
> explanation. In full it should have read:
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
> (Sample Data extract Sheet1).
> So, on the Source Data sheet to find text value 50-51 need to look in column
> B, to find text value 55-56 need to look in column C, to find text value 59-
> 60 need to look in column D etc. Each text value range is in a different
> column on the Source Data Sheet.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
> referencing is absoluted based on the information in my original Post. Do I
> need to create separate Named Formulas for each individual text value range
> to have the calculated intervals returned.
>
> If you can offer any further suggestions - most appreciated.
>
> Apologies for confusion.
>
> Thanks
> Sam

23. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Sam,

Domenic has given you a fine answer using formulas as you asked. I looked at
it from a macro standpoint and if you're interested in that version send me

CHORDially,
Art Farrell

"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance

and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row - starting with the most recent ( the

LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all

in
> column "B."
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from

the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract - Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB - is

a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate

the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet - using a separate column for each TEXT

value
> instance - interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT

value
> - this should be the basis for the FIRST calculated interval returned to

the
> New Sheet column B, Row380 should be used for the SECOND calculated

interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results - New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI

ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam
>
>
> --
> Message posted via http://www.officekb.com

24. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Thank you for all your help.

Your Formula does provide the expected results for the first Row - Row8 on
the Sheet I refer to as NewSheet (for the expected results). However, I get
the REF Error message on all subsequent rows.

I may have confused the sheet names in the various formulas, so, I'll just
run through what I've done:

1) Select cell B8 = NewSheet - expected results

2) Define the following references (Insert > Name > Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add - Defined as stated above

Name: Range
Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
)
Click Add - My data actually starts in Row no.19 but there are text labels in
Row no.18.

Name: NumCount
Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
Click Ok - I've taken Sheet1 in the COUNTIF Range to mean my NewSheet with
the expected results.
And this (Sheet1!\$B\$19<>Sheet1!\$A8) - to mean (SourceDataSheet!\$B
\$19<>NewSheet!\$A8)

Name: Array1
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
Click Add - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

Name: Array2
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
Click Ok - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8 - completed as suggested.

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:
=TRANSPOSE(Array2-Array1)-1

I'm not sure what I may have done incorrectly? I do get the correct results
for the first row on the NewSheet.

Any further help very much appreciated.

Thanks
Sam

Domenic wrote:
>Try the following...
>
>1) Select cell B8
>
>2) Define the following references (Insert > Name > Define):
>
>Name: BigText
>
>Refers to: =REPT("z",255)
>
>
>Name: Range
>
>Refers to:
>=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
>5536))
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
>
>Click Ok
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>Click Ok
>
>3) Select/highlight the range of cells in row 8 that will contain the
>results, for example, B8:AE8
>
>4) With the range of cells selected/highlighted, enter the following
>formula and confirm with CONTROL+SHIFT+ENTER:
>
>=TRANSPOSE(Array2-Array1)-1
>
>5) Copy the formula to other rows. To do so, select B8:AE8 first, then
>Copy/Paste.
>
>6) Use conditional formatting to hide the error values that will occur:
>
>a) Select B8
>
>b) Format > Conditional Formatting > Formula Is
>
>c) Enter the following formula:
>
>=ISERROR(B8)
>
>d) Choose 'White' as your font
>
>e) Click Ok
>
>f) Copy the format to other cells using the 'Format Painter' or 'Copy >
>Paste Special > Formats'.
>
>Hope this helps!
>
>> Hi All,
>>

>[quoted text clipped - 81 lines]
>> Thanks
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

25. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Try the following...

1) Select cell B8

2) Define the following references (Insert > Name > Define):

Name: BigText

Refers to: =REPT("z",255)

Name: Range

Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
5536))

Name: NumCount

Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)

Click Ok

Name: Array1

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Ok

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:

=TRANSPOSE(Array2-Array1)-1

5) Copy the formula to other rows. To do so, select B8:AE8 first, then
Copy/Paste.

6) Use conditional formatting to hide the error values that will occur:

a) Select B8

b) Format > Conditional Formatting > Formula Is

c) Enter the following formula:

=ISERROR(B8)

d) Choose 'White' as your font

e) Click Ok

f) Copy the format to other cells using the 'Format Painter' or 'Copy >
Paste Special > Formats'.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row – starting with the most recent ( the LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all in
> column “B.”
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract – Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB – is a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet – using a separate column for each TEXT value
> instance – interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT value
> – this should be the basis for the FIRST calculated interval returned to the
> New Sheet column B, Row380 should be used for the SECOND calculated interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam

26. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

>You'll get a #REF! error if there's no data in your range that meets the
>criterion or if there's only one cell that meets the criterion, and that
>cell is the first one, B19. Is this the case?

No to the above. My mistake as usual.

The reason I'm getting the #REF! error on all rows but the first: I
carelessly omitted part of the sentence in the last paragraph of my original
explanation. In full it should have read:

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
(Sample Data extract Sheet1).
So, on the Source Data sheet to find text value 50-51 need to look in column
B, to find text value 55-56 need to look in column C, to find text value 59-
60 need to look in column D etc. Each text value range is in a different
column on the Source Data Sheet.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
referencing is absoluted based on the information in my original Post. Do I
need to create separate Named Formulas for each individual text value range
to have the calculated intervals returned.

If you can offer any further suggestions - most appreciated.

Apologies for confusion.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

27. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Name: Range
> Refers to:
> =Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
> )
> Click Add - My data actually starts in Row no.19 but there are text labels in
> Row no.18.

Yes, I understand that your data starts in row 19. The reference starts
at row 18, and that's by design. I should have made note of it so that
there wouldn't be any confusion.

> I'm not sure what I may have done incorrectly? I do get the correct results
> for the first row on the NewSheet.

You'll get a #REF! error if there's no data in your range that meets the
criterion or if there's only one cell that meets the criterion, and that
cell is the first one, B19. Is this the case?

28. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Name: Range
> Refers to:
> =Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
> )
> Click Add - My data actually starts in Row no.19 but there are text labels in
> Row no.18.

Yes, I understand that your data starts in row 19. The reference starts
at row 18, and that's by design. I should have made note of it so that
there wouldn't be any confusion.

> I'm not sure what I may have done incorrectly? I do get the correct results
> for the first row on the NewSheet.

You'll get a #REF! error if there's no data in your range that meets the
criterion or if there's only one cell that meets the criterion, and that
cell is the first one, B19. Is this the case?

29. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Art,

Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk

Thanks
Sam

Art Farrell wrote:
>Hi Sam,
>
>Domenic has given you a fine answer using formulas as you asked. I looked at
>it from a macro standpoint and if you're interested in that version send me
>
>CHORDially,
>Art Farrell
>
>> Hi All,
>>
>> I require a Formula to calculate the intervals between each individual
>> occurrence of a TEXT value (the number of Rows between the LAST instance and
>> the PREVIOUS instance) in a column, and return each interval result to a
>> separate Column on the same Row - starting with the most recent ( the

>LAST)
>> occurrence.
>>

>[quoted text clipped - 12 lines]
>> ColC Houses the Text values 55-56
>> Where I have written the word blank (purely as a visual aid) in ColB - is

>a
>> Formula that has returned empty text.
>>

>[quoted text clipped - 22 lines]
>> Row numbers as explained above. The results should be returned in a
>> horizontal array to a new Sheet - using a separate column for each TEXT

>value
>> instance - interval calculation.
>>
>> In the above example, Row397 is the LAST Row with an instance of a TEXT value
>> - this should be the basis for the FIRST calculated interval returned to

>the
>> New Sheet column B, Row380 should be used for the SECOND calculated interval
>> returned to column C etc.

>[quoted text clipped - 29 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

30. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Thank you for all your help.

Your Formula does provide the expected results for the first Row - Row8 on
the Sheet I refer to as NewSheet (for the expected results). However, I get
the REF Error message on all subsequent rows.

I may have confused the sheet names in the various formulas, so, I'll just
run through what I've done:

1) Select cell B8 = NewSheet - expected results

2) Define the following references (Insert > Name > Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add - Defined as stated above

Name: Range
Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
)
Click Add - My data actually starts in Row no.19 but there are text labels in
Row no.18.

Name: NumCount
Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
Click Ok - I've taken Sheet1 in the COUNTIF Range to mean my NewSheet with
the expected results.
And this (Sheet1!\$B\$19<>Sheet1!\$A8) - to mean (SourceDataSheet!\$B
\$19<>NewSheet!\$A8)

Name: Array1
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
Click Add - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

Name: Array2
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
Click Ok - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8 - completed as suggested.

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:
=TRANSPOSE(Array2-Array1)-1

I'm not sure what I may have done incorrectly? I do get the correct results
for the first row on the NewSheet.

Any further help very much appreciated.

Thanks
Sam

Domenic wrote:
>Try the following...
>
>1) Select cell B8
>
>2) Define the following references (Insert > Name > Define):
>
>Name: BigText
>
>Refers to: =REPT("z",255)
>
>
>Name: Range
>
>Refers to:
>=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
>5536))
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
>
>Click Ok
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>Click Ok
>
>3) Select/highlight the range of cells in row 8 that will contain the
>results, for example, B8:AE8
>
>4) With the range of cells selected/highlighted, enter the following
>formula and confirm with CONTROL+SHIFT+ENTER:
>
>=TRANSPOSE(Array2-Array1)-1
>
>5) Copy the formula to other rows. To do so, select B8:AE8 first, then
>Copy/Paste.
>
>6) Use conditional formatting to hide the error values that will occur:
>
>a) Select B8
>
>b) Format > Conditional Formatting > Formula Is
>
>c) Enter the following formula:
>
>=ISERROR(B8)
>
>d) Choose 'White' as your font
>
>e) Click Ok
>
>f) Copy the format to other cells using the 'Format Painter' or 'Copy >
>Paste Special > Formats'.
>
>Hope this helps!
>
>> Hi All,
>>

>[quoted text clipped - 81 lines]
>> Thanks
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

31. ## Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi All,

I require a Formula to calculate the intervals between each individual
occurrence of a TEXT value (the number of Rows between the LAST instance and
the PREVIOUS instance) in a column, and return each interval result to a
separate Column on the same Row – starting with the most recent ( the LAST)
occurrence.

Source Data starts from Row19.
Text values of the same value will be in the same column; i.e. 50-51 all in
column “B.”
Locate when TEXT values LAST appeared together and Count back to their
PREVIOUS appearance together to get the required Count; i.e. the number of
Rows between the LAST appearance and the PREVIOUS appearance. Count from the
Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.

Sample Data extract – Sheet1:
Source Data starts from Row19 Column B
ColB means Column B etc
ColB Houses the Text values 50-51
ColC Houses the Text values 55-56
Where I have written the word blank (purely as a visual aid) in ColB – is a
Formula that has returned empty text.

Row19 ColB blank
Row20-Row83 ColB blank
Row84 ColB 50-51
Row85-Row133 ColB blank
Row134 ColB 50-51
Row135-Row136 ColB blank
Row137 ColB 50-51
Row138-Row141 ColB blank
Row142 ColB 50-51
Row143-Row172 ColB blank
Row173 ColB 50-51
Row174-Row266 ColB blank
Row267 ColB 50-51
Row268-Row297 ColB blank
Row298 ColB 50-51
Row299-Row379 ColB blank
Row380 ColB 50-51
Row381-Row396 ColB blank
Row397 ColB 50-51

So, the Formula needs to find each Row with the TEXT value and calculate the
interval for each occurrence or instance of the TEXT value by counting the
Row numbers as explained above. The results should be returned in a
horizontal array to a new Sheet – using a separate column for each TEXT value
instance – interval calculation.

In the above example, Row397 is the LAST Row with an instance of a TEXT value
– this should be the basis for the FIRST calculated interval returned to the
New Sheet column B, Row380 should be used for the SECOND calculated interval
returned to column C etc.

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

If the above is misaligned:
Row8
ColA =Text value 50-51
ColB = interval 16
ColC = interval 81
ColD = interval 30
ColE = interval 93
ColF = interval 30
ColG = interval 4
ColH = interval 2
ColI = interval 49
ColJ = interval 64

Thanks
Sam

--
Message posted via http://www.officekb.com

32. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Your suggested amendments to accommodate my scenario works perfect!

Thank you very much for your brilliant assistance and your time - very much
appreciated.

Cheers
Sam

Domenic wrote:
>Change/add the defined references, as follows (Insert > Name > Define):
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>
>Name: FirstRow
>
>Refers to:
>=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
>)
>
>Adjust the range B19:D19 accordingly. Also, you can replace the array
>constant {...} with a reference to a range of cells. So, for example,
>if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
>respectively, the MATCH part could be changed to...
>
>MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)
>
>You can use any other cells you want, but don't use row 18 on Sheet1
>since that would affect the calculations.
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)
>
>
>Name: Range
>
>Refers to:
>=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
>},0))
>
>With regards to the range and constant array, the same thing applies
>here as in the reference for FirstRow.
>
>Click Ok
>
>Hope this helps!
>
>> Hi Domenic,
>>

>[quoted text clipped - 39 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

33. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Arthur,

Thanks for sample file - great!

Cheers,
Sam

Sam wrote:
>Hi Art,
>
>Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk
>
>Thanks
>Sam
>
>>Hi Sam,
>>

>[quoted text clipped - 34 lines]
>>> Thanks
>>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

34. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

>You'll get a #REF! error if there's no data in your range that meets the
>criterion or if there's only one cell that meets the criterion, and that
>cell is the first one, B19. Is this the case?

No to the above. My mistake as usual.

The reason I'm getting the #REF! error on all rows but the first: I
carelessly omitted part of the sentence in the last paragraph of my original
explanation. In full it should have read:

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
(Sample Data extract Sheet1).
So, on the Source Data sheet to find text value 50-51 need to look in column
B, to find text value 55-56 need to look in column C, to find text value 59-
60 need to look in column D etc. Each text value range is in a different
column on the Source Data Sheet.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
referencing is absoluted based on the information in my original Post. Do I
need to create separate Named Formulas for each individual text value range
to have the calculated intervals returned.

If you can offer any further suggestions - most appreciated.

Apologies for confusion.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

35. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Change/add the defined references, as follows (Insert > Name > Define):

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Name: FirstRow

Refers to:
=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
)

Adjust the range B19:D19 accordingly. Also, you can replace the array
constant {...} with a reference to a range of cells. So, for example,
if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
respectively, the MATCH part could be changed to...

MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)

You can use any other cells you want, but don't use row 18 on Sheet1
since that would affect the calculations.

Name: NumCount

Refers to:
=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)

Name: Range

Refers to:
=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
},0))

With regards to the range and constant array, the same thing applies
here as in the reference for FirstRow.

Click Ok

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> >You'll get a #REF! error if there's no data in your range that meets the
> >criterion or if there's only one cell that meets the criterion, and that
> >cell is the first one, B19. Is this the case?

>
> No to the above. My mistake as usual.
>
> The reason I'm getting the #REF! error on all rows but the first: I
> carelessly omitted part of the sentence in the last paragraph of my original
> explanation. In full it should have read:
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
> (Sample Data extract Sheet1).
> So, on the Source Data sheet to find text value 50-51 need to look in column
> B, to find text value 55-56 need to look in column C, to find text value 59-
> 60 need to look in column D etc. Each text value range is in a different
> column on the Source Data Sheet.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
> referencing is absoluted based on the information in my original Post. Do I
> need to create separate Named Formulas for each individual text value range
> to have the calculated intervals returned.
>
> If you can offer any further suggestions - most appreciated.
>
> Apologies for confusion.
>
> Thanks
> Sam

36. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Sam,

Domenic has given you a fine answer using formulas as you asked. I looked at
it from a macro standpoint and if you're interested in that version send me

CHORDially,
Art Farrell

"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance

and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row - starting with the most recent ( the

LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all

in
> column "B."
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from

the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract - Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB - is

a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate

the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet - using a separate column for each TEXT

value
> instance - interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT

value
> - this should be the basis for the FIRST calculated interval returned to

the
> New Sheet column B, Row380 should be used for the SECOND calculated

interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results - New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI

ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam
>
>
> --
> Message posted via http://www.officekb.com

37. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Try the following...

1) Select cell B8

2) Define the following references (Insert > Name > Define):

Name: BigText

Refers to: =REPT("z",255)

Name: Range

Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
5536))

Name: NumCount

Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)

Click Ok

Name: Array1

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Ok

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:

=TRANSPOSE(Array2-Array1)-1

5) Copy the formula to other rows. To do so, select B8:AE8 first, then
Copy/Paste.

6) Use conditional formatting to hide the error values that will occur:

a) Select B8

b) Format > Conditional Formatting > Formula Is

c) Enter the following formula:

=ISERROR(B8)

d) Choose 'White' as your font

e) Click Ok

f) Copy the format to other cells using the 'Format Painter' or 'Copy >
Paste Special > Formats'.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row – starting with the most recent ( the LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all in
> column “B.”
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract – Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB – is a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet – using a separate column for each TEXT value
> instance – interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT value
> – this should be the basis for the FIRST calculated interval returned to the
> New Sheet column B, Row380 should be used for the SECOND calculated interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam

38. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Name: Range
> Refers to:
> =Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
> )
> Click Add - My data actually starts in Row no.19 but there are text labels in
> Row no.18.

Yes, I understand that your data starts in row 19. The reference starts
at row 18, and that's by design. I should have made note of it so that
there wouldn't be any confusion.

> I'm not sure what I may have done incorrectly? I do get the correct results
> for the first row on the NewSheet.

You'll get a #REF! error if there's no data in your range that meets the
criterion or if there's only one cell that meets the criterion, and that
cell is the first one, B19. Is this the case?

39. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Try the following...

1) Select cell B8

2) Define the following references (Insert > Name > Define):

Name: BigText

Refers to: =REPT("z",255)

Name: Range

Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
5536))

Name: NumCount

Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)

Click Ok

Name: Array1

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Ok

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:

=TRANSPOSE(Array2-Array1)-1

5) Copy the formula to other rows. To do so, select B8:AE8 first, then
Copy/Paste.

6) Use conditional formatting to hide the error values that will occur:

a) Select B8

b) Format > Conditional Formatting > Formula Is

c) Enter the following formula:

=ISERROR(B8)

d) Choose 'White' as your font

e) Click Ok

f) Copy the format to other cells using the 'Format Painter' or 'Copy >
Paste Special > Formats'.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row – starting with the most recent ( the LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all in
> column “B.”
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract – Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB – is a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet – using a separate column for each TEXT value
> instance – interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT value
> – this should be the basis for the FIRST calculated interval returned to the
> New Sheet column B, Row380 should be used for the SECOND calculated interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam

40. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Arthur,

Thanks for sample file - great!

Cheers,
Sam

Sam wrote:
>Hi Art,
>
>Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk
>
>Thanks
>Sam
>
>>Hi Sam,
>>

>[quoted text clipped - 34 lines]
>>> Thanks
>>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

41. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Thank you for all your help.

Your Formula does provide the expected results for the first Row - Row8 on
the Sheet I refer to as NewSheet (for the expected results). However, I get
the REF Error message on all subsequent rows.

I may have confused the sheet names in the various formulas, so, I'll just
run through what I've done:

1) Select cell B8 = NewSheet - expected results

2) Define the following references (Insert > Name > Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add - Defined as stated above

Name: Range
Refers to:
=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$65536)
)
Click Add - My data actually starts in Row no.19 but there are text labels in
Row no.18.

Name: NumCount
Refers to:
=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
Click Ok - I've taken Sheet1 in the COUNTIF Range to mean my NewSheet with
the expected results.
And this (Sheet1!\$B\$19<>Sheet1!\$A8) - to mean (SourceDataSheet!\$B
\$19<>NewSheet!\$A8)

Name: Array1
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
Click Add - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

Name: Array2
Refers to:
=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,19)),
ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
Click Ok - Sheet1 reference to \$A8 is my NewSheet with the expected results,
Sheet1 reference to \$B\$19 is my original Source Data.

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8 - completed as suggested.

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:
=TRANSPOSE(Array2-Array1)-1

I'm not sure what I may have done incorrectly? I do get the correct results
for the first row on the NewSheet.

Any further help very much appreciated.

Thanks
Sam

Domenic wrote:
>Try the following...
>
>1) Select cell B8
>
>2) Define the following references (Insert > Name > Define):
>
>Name: BigText
>
>Refers to: =REPT("z",255)
>
>
>Name: Range
>
>Refers to:
>=Sheet1!\$B\$18:INDEX(Sheet1!\$B\$18:\$B\$65536,MATCH(BigText,Sheet1!\$B\$18:\$B\$6
>5536))
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,Sheet1!\$A8)+(Sheet1!\$B\$19<>Sheet1!\$A8)
>
>Click Ok
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=Sheet1!\$A8)+(ROW(Range)=IF(Sheet1!\$B\$19<>Sheet1!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>Click Ok
>
>3) Select/highlight the range of cells in row 8 that will contain the
>results, for example, B8:AE8
>
>4) With the range of cells selected/highlighted, enter the following
>formula and confirm with CONTROL+SHIFT+ENTER:
>
>=TRANSPOSE(Array2-Array1)-1
>
>5) Copy the formula to other rows. To do so, select B8:AE8 first, then
>Copy/Paste.
>
>6) Use conditional formatting to hide the error values that will occur:
>
>a) Select B8
>
>b) Format > Conditional Formatting > Formula Is
>
>c) Enter the following formula:
>
>=ISERROR(B8)
>
>d) Choose 'White' as your font
>
>e) Click Ok
>
>f) Copy the format to other cells using the 'Format Painter' or 'Copy >
>Paste Special > Formats'.
>
>Hope this helps!
>
>> Hi All,
>>

>[quoted text clipped - 81 lines]
>> Thanks
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

42. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Art,

Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk

Thanks
Sam

Art Farrell wrote:
>Hi Sam,
>
>Domenic has given you a fine answer using formulas as you asked. I looked at
>it from a macro standpoint and if you're interested in that version send me
>
>CHORDially,
>Art Farrell
>
>> Hi All,
>>
>> I require a Formula to calculate the intervals between each individual
>> occurrence of a TEXT value (the number of Rows between the LAST instance and
>> the PREVIOUS instance) in a column, and return each interval result to a
>> separate Column on the same Row - starting with the most recent ( the

>LAST)
>> occurrence.
>>

>[quoted text clipped - 12 lines]
>> ColC Houses the Text values 55-56
>> Where I have written the word blank (purely as a visual aid) in ColB - is

>a
>> Formula that has returned empty text.
>>

>[quoted text clipped - 22 lines]
>> Row numbers as explained above. The results should be returned in a
>> horizontal array to a new Sheet - using a separate column for each TEXT

>value
>> instance - interval calculation.
>>
>> In the above example, Row397 is the LAST Row with an instance of a TEXT value
>> - this should be the basis for the FIRST calculated interval returned to

>the
>> New Sheet column B, Row380 should be used for the SECOND calculated interval
>> returned to column C etc.

>[quoted text clipped - 29 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

43. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

Your suggested amendments to accommodate my scenario works perfect!

Thank you very much for your brilliant assistance and your time - very much
appreciated.

Cheers
Sam

Domenic wrote:
>Change/add the defined references, as follows (Insert > Name > Define):
>
>Name: Array1
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
>
>
>Name: Array2
>
>Refers to:
>=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
>9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
>
>
>Name: FirstRow
>
>Refers to:
>=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
>)
>
>Adjust the range B19:D19 accordingly. Also, you can replace the array
>constant {...} with a reference to a range of cells. So, for example,
>if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
>respectively, the MATCH part could be changed to...
>
>MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)
>
>You can use any other cells you want, but don't use row 18 on Sheet1
>since that would affect the calculations.
>
>
>Name: NumCount
>
>Refers to:
>=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)
>
>
>Name: Range
>
>Refers to:
>=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
>},0))
>
>With regards to the range and constant array, the same thing applies
>here as in the reference for FirstRow.
>
>Click Ok
>
>Hope this helps!
>
>> Hi Domenic,
>>

>[quoted text clipped - 39 lines]
>> Thanks
>> Sam

--
Message posted via http://www.officekb.com

44. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Domenic,

>You'll get a #REF! error if there's no data in your range that meets the
>criterion or if there's only one cell that meets the criterion, and that
>cell is the first one, B19. Is this the case?

No to the above. My mistake as usual.

The reason I'm getting the #REF! error on all rows but the first: I
carelessly omitted part of the sentence in the last paragraph of my original
explanation. In full it should have read:

The Formula placed in the New Sheet and copied across column B to however
many columns there are Rows of TEXT values needs to be flexible enough to
identify TEXT values beyond Row397 and be adaptable to look for other TEXT
values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
(Sample Data extract Sheet1).
So, on the Source Data sheet to find text value 50-51 need to look in column
B, to find text value 55-56 need to look in column C, to find text value 59-
60 need to look in column D etc. Each text value range is in a different
column on the Source Data Sheet.

Expected Results – New Sheet:
ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
etc

Row8 50-51 16 81 30 93 30 4 2
49 64
Row9 55-56
Row10 59-60
etc

The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
referencing is absoluted based on the information in my original Post. Do I
need to create separate Named Formulas for each individual text value range
to have the calculated intervals returned.

If you can offer any further suggestions - most appreciated.

Apologies for confusion.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

45. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Change/add the defined references, as follows (Insert > Name > Define):

Name: Array1

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Name: Array2

Refers to:
=LARGE(IF((Range=NewSheet!\$A8)+(ROW(Range)=IF(FirstRow<>NewSheet!\$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Name: FirstRow

Refers to:
=INDEX(Sheet1!\$B\$19:\$D\$19,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"},0)
)

Adjust the range B19:D19 accordingly. Also, you can replace the array
constant {...} with a reference to a range of cells. So, for example,
if B17, C17, and D17 contained the label 50-51, 55-56, and 59-60,
respectively, the MATCH part could be changed to...

MATCH(NewSheet!\$A8,Sheet1!\$B\$17:\$D\$17,0)

You can use any other cells you want, but don't use row 18 on Sheet1
since that would affect the calculations.

Name: NumCount

Refers to:
=COUNTIF(Range,NewSheet!\$A8)+(FirstRow<>NewSheet!\$A8)

Name: Range

Refers to:
=INDEX(Sheet1!\$B\$18:\$D\$5000,0,MATCH(NewSheet!\$A8,{"50-51","55-56","59-60"
},0))

With regards to the range and constant array, the same thing applies
here as in the reference for FirstRow.

Click Ok

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> >You'll get a #REF! error if there's no data in your range that meets the
> >criterion or if there's only one cell that meets the criterion, and that
> >cell is the first one, B19. Is this the case?

>
> No to the above. My mistake as usual.
>
> The reason I'm getting the #REF! error on all rows but the first: I
> carelessly omitted part of the sentence in the last paragraph of my original
> explanation. In full it should have read:
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc. in different columns on the Source Data Sheet
> (Sample Data extract Sheet1).
> So, on the Source Data sheet to find text value 50-51 need to look in column
> B, to find text value 55-56 need to look in column C, to find text value 59-
> 60 need to look in column D etc. Each text value range is in a different
> column on the Source Data Sheet.
>
> Expected Results – New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> The Named Formulas "Range", "NumCount", "Array1" and "Array2" cell
> referencing is absoluted based on the information in my original Post. Do I
> need to create separate Named Formulas for each individual text value range
> to have the calculated intervals returned.
>
> If you can offer any further suggestions - most appreciated.
>
> Apologies for confusion.
>
> Thanks
> Sam

46. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Sam,

Domenic has given you a fine answer using formulas as you asked. I looked at
it from a macro standpoint and if you're interested in that version send me

CHORDially,
Art Farrell

"Sam via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
>
> Hi All,
>
> I require a Formula to calculate the intervals between each individual
> occurrence of a TEXT value (the number of Rows between the LAST instance

and
> the PREVIOUS instance) in a column, and return each interval result to a
> separate Column on the same Row - starting with the most recent ( the

LAST)
> occurrence.
>
> Source Data starts from Row19.
> Text values of the same value will be in the same column; i.e. 50-51 all

in
> column "B."
> Locate when TEXT values LAST appeared together and Count back to their
> PREVIOUS appearance together to get the required Count; i.e. the number of
> Rows between the LAST appearance and the PREVIOUS appearance. Count from

the
> Row ABOVE LAST appearance to the Row BEFORE PREVIOUS appearance.
>
> Sample Data extract - Sheet1:
> Source Data starts from Row19 Column B
> ColB means Column B etc
> ColB Houses the Text values 50-51
> ColC Houses the Text values 55-56
> Where I have written the word blank (purely as a visual aid) in ColB - is

a
> Formula that has returned empty text.
>
> Row19 ColB blank
> Row20-Row83 ColB blank
> Row84 ColB 50-51
> Row85-Row133 ColB blank
> Row134 ColB 50-51
> Row135-Row136 ColB blank
> Row137 ColB 50-51
> Row138-Row141 ColB blank
> Row142 ColB 50-51
> Row143-Row172 ColB blank
> Row173 ColB 50-51
> Row174-Row266 ColB blank
> Row267 ColB 50-51
> Row268-Row297 ColB blank
> Row298 ColB 50-51
> Row299-Row379 ColB blank
> Row380 ColB 50-51
> Row381-Row396 ColB blank
> Row397 ColB 50-51
>
> So, the Formula needs to find each Row with the TEXT value and calculate

the
> interval for each occurrence or instance of the TEXT value by counting the
> Row numbers as explained above. The results should be returned in a
> horizontal array to a new Sheet - using a separate column for each TEXT

value
> instance - interval calculation.
>
> In the above example, Row397 is the LAST Row with an instance of a TEXT

value
> - this should be the basis for the FIRST calculated interval returned to

the
> New Sheet column B, Row380 should be used for the SECOND calculated

interval
> returned to column C etc.
>
> The Formula placed in the New Sheet and copied across column B to however
> many columns there are Rows of TEXT values needs to be flexible enough to
> identify TEXT values beyond Row397 and be adaptable to look for other TEXT
> values: 55-56, 59-60 etc.
>
> Expected Results - New Sheet:
> ColA ColB ColC ColD ColE ColF ColG ColH ColI

ColJ
> etc
>
> Row8 50-51 16 81 30 93 30 4 2
> 49 64
> Row9 55-56
> Row10 59-60
> etc
>
> If the above is misaligned:
> Row8
> ColA =Text value 50-51
> ColB = interval 16
> ColC = interval 81
> ColD = interval 30
> ColE = interval 93
> ColF = interval 30
> ColG = interval 4
> ColH = interval 2
> ColI = interval 49
> ColJ = interval 64
>
> Thanks
> Sam
>
>
> --
> Message posted via http://www.officekb.com

47. ## Re: Count Intervals of Filtered TEXT values in Column and Return Count across a Row

Hi Arthur,

Thanks for sample file - great!

Cheers,
Sam

Sam wrote:
>Hi Art,
>
>Wouldn't mind seeing the macro. sam_518AThotmailDOTcoDOTuk
>
>Thanks
>Sam
>
>>Hi Sam,
>>

>[quoted text clipped - 34 lines]
>>> Thanks
>>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1