Count data to a limit and increment counter

I have a list of data and I'm trying to assign a numberical value to each line of code up to the number 30. Once I've reached 30 I'd like to increment the counter by one. For example, I have the code "APL" and it is replicated 62 times. I would like to have a counter that starts at 101 and every 30 iterations, the count will increase to 102, and finally the remaining two would be assigned to 103. Once the next unique value is reached the counter would increase to 104.

I know this sounds a little confusing so I've attached a sample sheet. The first tab is the data as I recieve it, and the second tab shows the data as I would like to see it.

Please note that the data will always be grouped together and in alphabetical order.

Hi Ricker090

If i understand correctly

Please Login or Register  to view this content.

Thinking about it, possibly should be

Please Login or Register  to view this content.

Hello Ricker090,

Here is a worksheet formula solution. This uses column "C" to track when a change occurs. Column "C" holds the count of the occurrences of data in column "A" from 1 to 30. If more than 30 occurrences are found then count starts back at 1. If the data changes then count resets to 1. The formulae in column "B" then increment the value in "B1" by 1 for each 1 in column "C".

In cell "B1" add the seed value

In cell C1 add the number 1

In cell B2 add this formula: =IF(C2=1,B1+1,B1)

In cell C2 add this formula: =IF(A2<>A1,1,IF(C1=30,1,C1+1))

Select B2 and C2 and drag the formulas down as far as needed. The attached workbook has the changes already made.