Class Rosters: How to populate automatically with a code...

[HistoryTeacher]

I have a list of 214 names in column A. Column B has a room number that students belong in next to each name. I need to create a list of names for each room number in columns C, D and E. When I put a number in column B next to the name of a student in column A how can I get the lists to populate automatically with student names in columns C, D & E? There must be a way!

[ben_hensel]

The fastest way is...

Highlight all the values (or just highlight columns A & B).

Then, one of two things:

1) In the ribbon, go to the Data tab, and in the Sort & Filter tab, click Filter.

2) Ctrl + Shift + L

This will allow you to filter column A to only display selected values from column B--if you only pick one room number, it will display only the names in column A that match that number in column B.

Then you could just copypasta from column A to C, D, and E. That wouldn't dynamically update with column A though....

Because you have your index value to the right of the lookup value, VLOOKUP is not going to be useful in its default... and you want a bunch of values anyway so we'd have to do some kind of exclusion going down the column... anyway, I'm going to go play with excel for a bit, but for now, I know filtering will work.

[HistoryTeacher]

The fastest way is...

Highlight all the values (or just highlight columns A & B).

Then, one of two things:

1) In the ribbon, go to the Data tab, and in the Sort & Filter tab, click Filter.

2) Ctrl + Shift + L

This will allow you to filter column A to only display selected values from column B--if you only pick one room number, it will display only the names in column A that match that number in column B.

Then you could just copypasta from column A to C, D, and E. That wouldn't dynamically update with column A though....

Because you have your index value to the right of the lookup value, VLOOKUP is not going to be useful in its default... and you want a bunch of values anyway so we'd have to do some kind of exclusion going down the column... anyway, I'm going to go play with excel for a bit, but for now, I know filtering will work.

Thank you for your suggestion. I could not get VLOOOKUP to work and I also tried DGET functions. Ideally, I would like to create a template that would automatically distribute (copy) names to a room number at the top of each column (C,D,E) as they were entered into column A. Would VLOOKUP work if I switched the name and room number colums (A & B)?

[Cutter]

See if the attached is what you want