Find the max for each employee for each column
I am stuck on an issue with my Skill Matrix, which is now very business critical. I have a table with 300+ columns, one for each skill. Each row correspond to a training record with a date where an employee designated by a number has been trained.
A second table summarize the current status so it should show the max of each column / each skill for the employee number corresponding to the row. There is in this table only 1 row per employee number. I have been trying an Index(Collect( combination but I couldn't figure out a formula for the whole sheet and not only for one column. As I have 300+ columns, I would lke to find a formula for the whole sheet so I do not have to change the scross sheet reference for every columns.
Here is the desired output:
Could you please help me finding the right formula for this? :)
If you are still following, I get another challenge!
I have a third table which are the requirements for each position, with the same skills columns. Each row correspond to a position .
The second challenge I have got is to compare for each skill (so each column), the difference between the Max we just calculated before and the requirement for each position.
Let's make an example!
Patrick Smith our truck driver should have for the column Skill 1 +2, Skill 2 -1 Skill 3 -1 and so on.
I will be really grateful if you have any tips or feedback for me. Please note that I would like to keep the structure as it is linked wiht a form, but all feedbacks will be appreciated! ;)
Have a nice week end!
Help Article Resources
Check out the Formula Handbook template!