How to organize employee data by team in a separate Google Sheets workbook?

Other Questions Google Sheets
1

Hey everyone, I’m struggling with a Google Sheets problem. I’ve got a main sheet with all our staff info. It’s got their names and which team they’re on. Now I want to make a new sheet that groups people by their teams. I tried a few things but I’m stuck.

Here’s what I’m working with:

  • Sheet 1: Has columns for names and team assignments
  • Sheet 2 (new): Should list employees sorted by their teams

I’m not sure how to pull the right info from the first sheet to the second one. Any ideas on how to set this up? I’d really appreciate some help or tips on getting this working. Thanks!

2

hey there ClimbingLion! have u tried using QUERY function? it’s super useful for this kinda stuff. u can set it up like:

=QUERY(Sheet1!A:B, "SELECT A WHERE B = ‘Team Name’ ")

just replace ‘Team Name’ with each team. hope this helps! lemme know if u need more info

3

I’ve actually tackled this issue before at my workplace. What worked well for us was using a combination of UNIQUE and FILTER functions. Here’s a formula you could try:

=ARRAYFORMULA(UNIQUE(FILTER(Sheet1!B:B, Sheet1!B<>“”)))

This will give you a list of unique team names in Sheet2. Then, for each team, you can use:

=FILTER(Sheet1!A:A, Sheet1!B=B1)

Where B1 is the cell with the team name.

This approach creates a dynamic list that updates automatically when you add or remove employees from Sheet1. It’s been quite reliable for us, especially when dealing with frequent staff changes. Just make sure to leave some blank rows between team sections to accommodate potential growth.

4

I’ve encountered a similar challenge in my work. One effective solution is using the FILTER function combined with SORT. Here’s a formula you can try:

=SORT(FILTER(Sheet1!A:B, Sheet1!B<>“”), 2, TRUE)

This will pull all non-empty rows from Sheet1, sort them by team, and display the results in Sheet2. You’ll get a clean list of employees grouped by their teams.

If you need separate sections for each team, consider using QUERY as mentioned earlier, but within a more complex array formula. It requires more setup but offers greater flexibility for customization.