Data cleaning in excel

I have a data file in which team members evaluate each other in terms of leadership. Items need to be manually sorted in excel before we can use it for our statistical analysis. Regarding the Excel sheet: - Each row includes survey responses from a single person over three days - there are columns that mention the persons' names and teams - In the current (survey) format, a set of columns describes how the team members evaluated each other’s' leadership values. The first column of the set is the rating they gave themselves. The second column is the name of the first team member they rated in the original survey form. The third column contains the rating the person gave the first member they rated in the survey. From here, it continues with all the remaining team members. Task: Rather than the structure "1) own rating 2) first other name 3) first other rating 4) second other name 5) second other rating...." we need the values a person received in their own row. For this, we need new columns in the excel sheet with this information: 1) own rating 2) first rating another team member gave this person 3) "second rating another team member gave this person..." Moreover, there are some problems in the data that I would like you to highlight. For example, if there are two rows for the same name of a team member, highlight them. Similarly, some team members evaluate people who we don’t have in our data sheet. Please highlight their names and ratings, too. There are around 100 teams and 546 team members who evaluated each other over 3 days. The task should thus not take more than a few hours of excel coding. Note: we I already had an assistant code some of the items in the gray area at the right side of the excerpt I am attaching. These exist for a fair share of the items but should be redone since we noticed a lot of mistakes. These attachments should help clarifying the task: 1) Screenshot of original survey item 2) Excerpt of the actual excel data sheet. Column A: team name, Column B: team member name, Column C-Z: Leadership ratings of day 1, Column AA-AV: Leadership ratings of day 2, AW-BP: Leadership ratings of day 3, Column BQ: Space holder, Column BR-CX: previously done ratings (these have to be redone, too) 3) Demo of how the data coding should work (yellow is what one can expect in the original sheet, green is what we need.