Aggregating Excel cell contents that match a label [migrated]
- by Josh
I'm sure this isn't a terribly difficult thing, but it's not the type of question that easily lends itself to internet searches.
I've been assigned a project for work involving a complex spreadsheet. I've done the usual =SUM and other basic Excel formulas, and I've got enough coding background that I'm able to at least fudge my way through VBA, but I'm not certain how to proceed with one part of the task.
Simple version: On Sheet 1 I have a list of people (one on each row, person's name in column A), on sheet 2 I have a list of groups (one on each row, group name in column A). Each name in Sheet 1 has its own row, and I have a "Data Validation" dropdown menu where you choose the group each person belongs to.
That dropdown is sourced from Sheet 2, where each group has a row. So essentially the data validation source for Sheet 1's "Group" column is just "=Sheet2!$a1:a100" or whatever.
The problem is this: I want each group row in Sheet 2 to have a formula which results in a list of all the users which have been assigned to that group on Sheet 1. What I mean is something the equivalent of "select * from PeopleTab where GROUP = ThisGroup". The resulting cell would just stick the names together like "Bob Smith, Joe Jones, Sally Sanderson"
I've been Googling for hours but I can't think of a way to phrase my search query to get the results I want.
Here's an example of desired result (Dash-delimited. Can't find a way to make it look nice, table tags don't seem to work here):
(Sheet 1)
Bob Smith - Group 1 (selected from dropdown)
Joe Jones - Group 2 (selected from dropdown)
Sally Sanderson - Group 1 (selected from dropdown)
(Sheet 2)
Group 1 - Bob Smith, Sally Sanderson (result of formula)
Group 2 - Joe Jones (result of formula)
What formula (or even what function) do I use on that second column of sheet 2 to make a flat list out of the members of that group?
