hi

let's say i have this set of numbers

105 120 276 120 shows up here

212 350 274

140 370 286

310 336 276

250 331 296

270 120 376 120 shows up here

280 310 216

289 120 215 120 shows up here

280 330 216

299 250 212

3 columms wide, and x rows deep, in this case 10

what i am looking for is a short and sweet way - can be a macro, but not usng filled down formulas - to find the followig:

1) the last time any given number appeared (the bottom number being the very last appearance), for example, '120' shows up three rows ago

2) the longest stretch of rows the number fails to appear; for example, the '120's last time to appear was 3 rows up. the time before that 2 rows between appearances, before that 5. so looking at the data the longest stretch of '120' not appearing was 5 rows.

3) the number of times any particular number might appear in any particluar number of 'rolling' rows. for example the number to target will be 276, and the 'rolling' number of rows will be set to 4

105 120 276-----

212 350 274 | -----

140 370 286 2x | |-----

310 336 276----- 1x | |

250 331 296----------- 2x |

276 120 376-----------------

280 310 216 etc

289 276 215

280 330 216

299 250 212

in the seven complete cases where 4 rows of numbers can be examined, on a 'rolling' basis, the maximum number of times '276' shows up in any four row sectiion is 2 and the minimum is 1.

i know how to get this the long way, but if there was a single cell formula that could do the same thing, or a nice macro to shred the numbers in a few seconds, that'd be really great to discover.

many thanks in advance

tx

## Bookmarks