Consecutive occurrences of an item in a list over a period

[Bucks260]

I track over every week/period various items for my company, and then how many weeks that specific item has been in the top 10 list for a specific metric (not relevant). I do this by simply dumping the top 10 in a giant cumulative list every week and running a simple countif next to the same week's list (on another sheet)to see how many times it has been there total. However now my boss would like to see how many consecutive weeks an item has been on the list of late. I'm blanking right now and can't think of how I would do this. Please help. Thanks.

For the list,I just have the product names in col A and week reference in col B.

 

[inactiveuserps07]

@Bucks260, if, for instance, you used cell G2 to enter your search criteria (i.e., item name to search), you could use this formula in another unused cell to give you consecutive weeks for that item:

=SUMPRODUCT(--($A$2:$A$5000=$G$2)*(ISNUMBER(MATCH(($B$2:$B$5000)-1,$B$2:$B$5000,0))))

Adjust the range to suit your situation.

Keep in mind that a result of 1 would mean, in effect, "no consecutive weeks" (e.g., only ever a one-week run at a time so far).

 

[Bucks260]

@Bucks260, if, for instance, you used cell G2 to enter your search criteria (i.e., item name to search), you could use this formula in another unused cell to give you consecutive weeks for that item:

=SUMPRODUCT(--($A$2:$A$5000=$G$2)*(ISNUMBER(MATCH(($B$2:$B$5000)-1,$B$2:$B$5000,0))))

Adjust the range to suit your situation.

Keep in mind that a result of 1 would mean, in effect, "no consecutive weeks" (e.g., only ever a one-week run at a time so far).

Hey Erik, thanks for replying. The formula doesn't seem to be putting out the right results though. I put it in and double checked one entry.

Maybe I could explain it more. Every week I add 50 new records to it and by now there are about 500 total. Some items have been on the list 7-8 times and others only 1 time. Next to the item names, there is the week reference: 61, as in period 6 week 1, or 62, period 6 week 2.

What I would like is to see from the bottom, 82, Period 8 Week 2, is to count up, and see if there have been any items appear consecutively, so more specifically, that would mean finding instances of item1 82, item1 81, item1 74, etc would be 3 consecutive weeks.

 

[inactiveuserps07]

Ah, so you're actually using a made-up system of numbers that aren't actually consecutive and are, in fact, somewhat unpredictable (i.e., 74 leads to 81, and you might also have months where the system ends in 5 rather than 4 (if it's a five-week month). It's not really designed, then, for testing "consecutive" occurrences, since it's a fabricated system.

I'm heading off on vacation, but that information will be important for others as they view the thread and potentially suggest approaches. Personally, I'd recommend converting back to a linear counting method where the first week of January is 1, second week is 2, etc. Or you could run a helper column formula that converts your numbering system back to linear, where my suggested formula would work.