Report Grouping based on Multi-Select Drop Down, with the same "data" listed under multiple groups

bonzemail · 09/16/21

I have a list of technicians who support calls from different phone queues. I need a report that will group all technicians who support each one. And as in any call center, technicians never support a single queue and will therefore need to show up under each queue group.

This is what my sheet would contain:

And this is what I would like to have my report look like:

At first I tried creating reports to contain each skill, and then soon realized I cannot add "reports" to a report, only sheets to a report. I've thought about having several sheets, but then the question is how do I keep them all updated when updating the master sheet? I only want one source for entry and updates, rather than having to go manually update several sheets with the same information.

Any advise would be appreciated!

Sean Morgan · 09/20/21

Hey @bonzemail

So this is a great idea, but my solution is slightly different. Rather than having the queues as a multi select, I have the Contacts as a multi select:

Here's what my Sheet looks like (please note I have hidden my colleagues names)

Then, I've been able to replicate your desired Report:

I feel this would be the best method of reaching your desired layout. It may take some reconstruction in your Master Sheet, but to achieve this method of a Report, this would be the proffered method.

When doing this, you should list the queue numbers in a Column that is not the Primary. Having these in the Primary then shows these numbers in the Report, making the Report less appealing. Furthermore, the Group I have is for the "Queues" Column that I created.

Please reach out if you have any questions

Regards

Sean

bonzemail · 09/20/21

Hey Sean!

Thank you for the suggestion! Unfortunately I have around 100 columns attached to each technicians row storing different information, therefore reconstruction of the sheet isn't really an option. I am going to look into cell linking to see if there are options through that, but otherwise I am still a bit stumped.

Genevieve P. · 09/23/21

Hi @bonzemail

You could use a JOIN(COLLECT formula to bring together all the techs who are assigned to a specific queue group, but it would bring them into one cell, versus parsing them out down multiple rows.

You can add a CHAR(10) line break between each agent, so the cell will have multiple lines. It would look something like this:

Source Sheet

Screen Shot 2021-09-23 at 7.45.16 PM.png

Formula Sheet

Screen Shot 2021-09-23 at 7.46.32 PM.png

Formula used:

=JOIN(COLLECT({Tech Name}, {Group MultiSelect}, HAS(@cell, [Group Header]@row)), CHAR(10))

Would this work for you?

Cheers,

Genevieve

bonzemail · 09/24/21

That is a great idea! The only reason it won't work is I really need to see a list of everyone in Group 1, but also show me what other groups they are in. Executive decisions have to be made based on who is in each group, followed by what other groups they are also in.

I am starting to worry what I need isn't possible.

Genevieve P. · 09/24/21

Hi @bonzemail

You're right, it may not be possible to create your desired formatting directly from your source sheet based on the current set-up.

I would suggest having two separate sources of data: one to show each Queue and the Techs associated (as I have above), and one to show each Tech and their Queues (like in your original sheet, perhaps a Report built off of this just showing the two columns).

Then what I would do is put this in a Dashboard so you can see both side-by-side. This way when you locate a Tech in a specific Queue, you can look over into the second resource to identify the other queues.

Something like this?

Screen Shot 2021-09-24 at 8.10.19 PM.png

If you use a COUNTM function, you can count how many of your multi-select Skills are associated with each person (in a helper column in your source sheet) and then turn that into a bar chart, like above.

Cheers,

Genevieve

Report Grouping based on Multi-Select Drop Down, with the same "data" listed under multiple groups

Answers

Help Article Resources

Categories