Formula to Repeat and increment Numbers and reset when ID changes

Question

I am looking for a start in the right direction and I hope someone on this forum has run into this issue. I have an excel table with 24K jobs on it and a technician assigned to every job. These technicians have 40 weeks to complete all the jobs assigned. I have a helper table with each technician’s id and how many jobs per week then need to complete all the work. I have sorted the jobs by geographic area for efficiency. I need a formula that will look at the Technician id and if they are receiving 3 jobs per week that it will number the first 3 with a 1, and the next 3 with a 2 and so on. And when it switches Technician it would reset the counter.

In the example below Tech 1 is assigned 3 jobs per week, and Tech 2 has 2 jobs per week.

| JobID | Tech   | Grouping |
|-------|--------|----------|
| BK025 | Tech 1 | 1        |
| CD044 | Tech 1 | 1        |
| DE024 | Tech 1 | 1        |
| DE031 | Tech 1 | 2        |
| DE035 | Tech 1 | 2        |
| FT083 | Tech 1 | 2        |
| IR004 | Tech 2 | 1        |
| IR006 | Tech 2 | 1        |
| IR052 | Tech 2 | 2        |
| IR061 | Tech 2 | 2        |
| IR062 | Tech 2 | 3        |
| IR072 | Tech 2 | 3        |

I have been searching SO and Google looking for an answer but may not be using the correct key words.I have found this formula =ROUNDUP((ROW()-offset)/repeat,0) -- Found on exceljet -- which will work, but in order to get it to work properly I would have to filter to each tech individually.

Answer 1

Assuming your helper table is something like in the screenshot below, you could use an approach like the following:

=ROUNDUP(COUNTIF(B$2:B2,B2)/VLOOKUP(B2,$E$1:$F$3,2,0),0)