Looking for a less complicated/more efficient way to aggregate data from a table in Google Sheets?-CodePudding

I have a table with some data that I am trying to organize and be used in different tables. Column 1 is a list of people, column 2 is their services, and column 3 is the employee responsible for that service. Column 1 will sometimes have multiple services and employees assigned to them. This is what the table looks like:

Client	Service	Employee
Client1	Design	James
Client2	Writing	Frank
Client3	Video	Jessica
Client3	Design	Amy
Client2	Design	Amy
Client3	Writing	Frank

First I use some vlookup and if formulas to get each clients' services organized into a separate table, I get this:

Client	Design	Writing	Video
Client1	James-Design
Client2	Amy-Design	Frank-Writing
Client3	Amy-Design	Frank-Writing	Jessica-Video

Next I use query to organize each clients' employees/services into columns where the header represents the service they don't have so that I can index them into the first table

Client	Design	Writing	Video
Client1		James-Design	James-Design
Client2	Frank-Writing	Amy-Design	Amy-Design, Frank-Writing
Client3	Frank-Writing, Jessica-Video	Amy-Design, Jessica-Video	Amy-Design, Frank-Writing

Finally, I use index match to pull the data from that last time into a new column in the first table

Client	Service	Employee	Other Service/Employee
Client1	Design	James
Client2	Writing	Frank	Amy-Design
Client3	Video	Jessica	Amy-Design, Frank-Writing
Client3	Design	Amy	Frank-Writing, Jessica-Video
Client2	Design	Amy	Frank-Writing
Client3	Writing	Frank	Amy-Design, Jessica-Video

I'm hoping there is a cleaner way of doing all of this. This method works fine, but I end up having multiple tables just to organize the data and the index-match function used in the final step needs to be dragged down the column since I can't use it in an arrayformula. There is probably a way to utilize query to get this done, but I often get lost in how it works.

tl;dr Table listing clients, services, and assigned employees. Client3 appears in 3 separate rows, has 3 different services, and 3 assigned employees. Want to add a 4th column to show the other services and employees.

CodePudding user response：

use in row 1:

={"Other Service/Employee"; INDEX(REGEXREPLACE(TRIM(REGEXREPLACE(IFNA(VLOOKUP(A2:A, 
 TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:A&"♦", ROW(B2:B), C2:C&"-"&B2:B&","}, 
 "select max(Col3) where Col1 <>'♦' group by Col2 pivot Col1"),,
 9^9)), "♦")), 2, )), C2:C&"-"&B2:B&",", )), ",$", ))}