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