Help putting data in the correct column automatically

Mariann Carmen

Hello.

I'm working with a worksheet where I'm trying to put a Workers management team into the correct column based on the Managers Management Level. I'm able to see the flow of an organization by using an INDEX(MATCH()) formula (=INDEX({Manager}, MATCH(Worker@row, {Worker}), 1), but how do I get the data to show up in the correct column?

In the example below, the data highlighted should be showing up in the Vice President / ML3 columns.

Any help would be greatly appreciated.

Mariann Carmen

I feel like I need an additional piece of criteria in the Formula for it to know where to place the data when it's found.

Michelle Choate 2

You might need an INDEX/COLLECT formula to parse out multiple criteria to fill the columns.

Mariann Carmen

@Michelle Choate 2 Will you please give me an example on how to write this formula based on my example above? I've looked at the Formula, but I can't figure out how to use it correctly.

Michelle Choate 2

I would have to see your current formula and the references that you have in order to help you rewrite it. Can you please provide what it looks like? Also happy to jump on a zoom call whenever if you wanted to go over it in person.

Mariann Carmen

@Michelle Choate 2 I posted the formula along with the image above.

Thank you for the assist. ❤️

Paul Newcome

It would be something along the lines of

=INDEX(COLLECT({Manager}, {Worker}, @cell = Worker@row, {Level}, @cell = "Vice President"), 1)

Mariann Carmen

Thank you @Paul Newcome I'll give that a try.

Mariann Carmen

@Paul Newcome Hello again.

Thank you, I think I'm getting closer.

If I have/need to add an Employee ID to identify the Vice President, how would I do that with this function?

=INDEX(COLLECT({Manager}, {Worker}, @cell = Worker@row, {Level}, @cell = "Vice President"), 1)

I have instances with Employees who have the same name, so I need to use the Employee ID to identify the correct chain of command (otherwise, it defaults to the first instance's chain of command). I tried swapping out {Manager} for {Manager ID}, but that didn't work like I expected it to (see below).

=INDEX(COLLECT({Manager ID}, {Worker}, @cell = Worker@row, {Mgmt Level}, @cell = "Director"), 0)

I keep getting an "Invalid Column Value" error for both of the above functions. Any thoughts on how I can overcome this?

Paul Newcome

Do you have some additional screenshots for context?

Mariann Carmen

Yes, it would be just like the one above, but I've added the Employee ID at the beginning for the Worker. I'm wondering if I should be adding it for Team Lead, and the other management levels, as well.

Paul Newcome

What does your source data look like?

Mariann Carmen

I have all of this data in one sheet.

I couldn't screen capture it all in one graphic, so I needed to send you two. Is this what you are asking for?

The Manager will also show up as a Worker, with their Management Level for them as well (which could range from Team Lead to CEO), and their Manager ID will be their Employee ID (ID's are assigned once to all employees and DO NOT change for the duration of employment).

I appreciate the assistance. ❤️

Paul Newcome

Ah. Ok. If it is all in one sheet, you will need to manually add the IDs for each level.

Mariann Carmen

They already exist, and it's still not working for me. Not sure what I'm doing wrong.

Michelle Choate 2

Hello @Mariann Carmen. If you want to jump on a quick zoom to see what is going on, feel free to message me via email michelle.choate@outlook.com :) Happy to take a peak at your formulas and troubleshoot with you.