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

## Bookmarks