Creating A Roster With Excel

Hi,

I'm working on an Excel document that organizes people into groups/teams/table assignments. I would like to generate a roster of people in each group--e.g. those who are in Bill's Group, those who are in Bob's Group. The people who have signed up are in Column A. In Column B will be the group assignment where I'll just type "Bill" etc.

What I am trying to do is create a place somewhere else on the worksheet that has a running list of those who are in Bill's and Bob's groups, based on the data from Column A and B. Thus, when the field in column B says "Bill" the person's name from Column A is listed in the roster section under Bill's name.

My list is actually quite long and I already use data sorting and counting functions to find out how many are in each group/team, but I would like to be able to quickly see who is in a group without having to sort data and make changes on the fly to move people from group to group.

Here's an example of what I am looking for:

Example:

Column A Column B
Jim F Bill
Tom R Bob
Tony T Bob
Fred B Bill
Rick G Bill
Eric N Bob

I would like the above information to automatically produce a list such as these that will change on the fly as I change Bill's and Bob's names in the columns:

Bill's Roster
Jim F
Fred B
Rick G

Bob's Roster
Tom R
Tony T
Eric N

Thanks for you help!

see attachment, is this close?

Hi Robert,

This is excellent! I was able to work with the data and make your formula's work. I've now added lots of people and groups and it's all working very smoothly. Thank you so much!

One last question, in the roster column under the team leader's name, when there is no name(say Bob's team has 10 people, but Bill's only has 5) in the final blank spaces beneath Bill, the dreaded #num! apprears. How can I surpress that so that the field is just blank.

The formula in the cell is:

=IF(Q40="","",OFFSET($C$15,SMALL($E$16:$E$200,$E$13+Q40),0))

Thanks again for your help,

Russ

Hi Robert,

I was working with the cells and figured out that last problem. If I use conditional formatting and set the condition to "Formula Is" and "=ISERROR(C15)" and make the formatting a white font, the text disappears and that's all I need. If you know a better workaround, I'd take that too.

Again, thank you for your help with the roster. I'm really impresssed with your creative solutions and side-calculations that pull all the data together. It's the kind of out-of-the-box thinking that way beyond my capabilities. Thank you!

Best regards,

Russ