I have a table with the date at the top and employee names down the side. For each date range, an employee is assigned a number 1-3 in Column A (for the 1st week; Column I for the 2nd week, etc. as there is other information that will either be calculated with a formula or entered manually in the 7 coloumns between each date range). Column A, I, etc, we will call "score". Column B, J, etc, which we will title "# of Consecutive Weeks". I'm looking for a formula that will compare that week's score to the previous week's score and return the count of how many weeks the employee was at that score consecutively.
For example, if Employee A has a score of 1 for the first week and a score of 1 for the second week, I need the "# of Consecutive Weeks" for the 2nd week to show 2. If the 3rd week, the employee has a score of 2, "# of Consecutive Weeks" would show 1 but if the employee scored a 1, the "# of Consecutive Weeks" would show 3.
Each week the employee has two scores based on seperate criteria so each employee has two lines. I also have a spreadsheet that pulls just those scores over with the employee names as headers and the dates down the side. I hoped that putting each set of scores in columns for each employee would provide more solutions.
I've looked all over forums and search engines looking for something that would work but haven't had luck. Maybe it's simple and I'm making it too complicated? I'm looking to do the calculation without the use of VBA as once the workbook is created, it will be sent for someone else's use that only has basic excel knowledge and multiple people will be using the workbook.
Bookmarks