Create list from matrix in Excel

Question

Hi everyone, 

At my company, we work with 270-some clients. Each client has a planner, and an associate on their team. In addition to this, each client is assigned a month, and we also give them a complexity-rating of sort- A, B, or C. This all fits well in an excel list, with each of these pieces showing in a different column. 

However, when we have an employee promoted, or if an employee leaves, or if we gain an employee, we have to redistribute clients. However, when we redistribute, we want to be sensitive of how many each planner and associate has in each month, and that they have the right number of each type of complexity. We would like to be able to visualize this and move clients around in a matrix. Please see the picture. This shows how we can see what planner each client has (on the left) and what associate each client has (on the top)

I have the cells conditioned to fill with the appropriate color, if data is entered, and I have an automatic count going, if data is entered in that area. 

What I would like to do is this: I want to have a list that reflects this, and any changes that might occur. Please see picture. I need formulas to put into the list, so that depending on where the client is, in the matrix, the list populates with the corresponding planner, associate, month, and client type. 

I have been able to a VLookUp & Choose formula to automatically update a list for the values on the left in the matrix picture (the planner and client type). This is the formula: =VLOOKUP(J5,CHOOSE({1,2},C3:C14,B3:B14),2,FALSE). So say I moved Thor from Dan as the planner to Todd, in the matrix, the formula would change that in the list. 

However, I can't figure out a formula that will automatically find and update the list for the values across the top (the associate and month).

Does anyone have any ideas, suggestions or tips?

Answer 1

Hi 

It is difficult to reproduce your scenario on our side, 

May I request you to post the file with dummy data (no production / real data)

to Onedrive or any other cloud sharing site and post the link here?

It will help us to give you a prompt and right solution.

If you need help about how to do it please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Regards

Answer 2

Hello, 

I'm not sure if you saw the link I previously posted, I decided to take it down, because I'm not sure if I am able to share the spreadsheet safely!