Try the following...
1) Select cell B8
2) Define the following references (Insert > Name > Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add
Name: Range
Refers to:
=Sheet1!$B$18:INDEX(Sheet1!$B$18:$B$65536,MATCH(BigText,Sheet1!$B$18:$B$6
5536))
Click Add
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)))
Click Add
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
Bookmarks