Excel Reference and count matrix

Copper Contributor

Hi Community,

 

A bit of a complex one here, I have a excel output from Microsoft Forms that I want to use as a data point for a matrix.

 

SamSmithIPU_1-1630405333864.png

(Sheet Name: Paste Results)

 

From the responses provided, I want to be able to reflect the responses in a matrix an another sheet, below is an example of what I would like the output to appear like.  Self and Manager is simple enough and I have done that with and If statement and Vlookup as it is only 1 respondents response that I am after, however for Team and Peers it gets a bit more tricker as I want to count how many of each was selected for this question.  For example if 3 Peers submitted 3 on this question, then in cell E8 I want this to show as 3, whilst if also 5 peers submitted 5 then in cell G8 it will show as 5, in total for this example there would be 8 peers responding to this form.  The same would be applied for Team members.

 

SamSmithIPU_4-1630407323706.png

(Sheet Name: Result Graphs)

 

Below is another sheet within the workbook where someone can fill out who is the person it is about, the manager, peers and team members.  This is where the formulas would reference against for the search in the data output from Forms.

 

SamSmithIPU_3-1630405769454.png

(Sheet Name: Relations)

 

Something to put out there, the number of Team Members and Peers can change for each person the form is submitted about and the order of the responses is purley set in order of when users responded, so ideally the solution needs to be flexible and not hard coded.  For example, if the answer is Vlookup's I dont want to have to enter 100 Vlookup's to cover the range of possibily 60 peers or team members responding.

2 Replies
Can you post a sample data? It will help us to assist you with an answer.

@Juliano-Petrukio Here is the file, it is all dummy data.