Get names of all students who applied to each college

[jefhal]

More on the previous spreadsheet where I now have:
1. a list of student names in Column A of sheet 1
2. a list of the colleges that they applied to in Columns B through
in sheet 1
3. a list of colleges in Column A of sheet 2

I now need to fill column B of sheet 2 with the names of all th
students who applied to each college. E.G.:

Sheet 1
Fred Harvard Amherst UCONN UNH
John Yale Bates UNH UCLA
Jeff McGill Yale UVM MIT
Stan Yale Harvard UNH McGill

Sheet 2
Amherst Fred
Bates John
Harvard Fred, Stan
McGill Jeff, Stan
MIT Jeff
UCLA John
....

I know how to concatenate cells and I know how to count the instance
of each college, but I don't know how to retrieve the names of th
student from the rows where the college name occurs on Sheet 1...

I've looked at index, vlookup, countif, etc. but no luck so far...

Thanks for your help.

Jeff
Tech Support
Souhegan H.S.
Amherst, New Hampshire
US

 

[Biff]

Hi Jeff!

I can get all the names and match them up with a college
but I can't get more than one name per cell.

eg: Jeff, Stan

No can do!

I can get Jeff in col B, Stan in col C, etc.

If that's acceptable, here's how:

Assume on sheet1 names are in A1:A4 and colleges are in
B1:E4.

On sheet2 colleges are in the range A1:A6.

On sheet2 array enter this formula in B1: CTRL,SHIFT,ENTER

=INDEX(Sheet1!$A$1:$A$4,SMALL(IF($A1=Sheet1!$B$1:$E$4,ROW
($A$1:$A$4)),COLUMN()-1))

Now copy down to A6 then across until you get a full
column of #NUM! errors. With that range still selected hit
F5. Click the Special button. Select Formulas and uncheck
all the options *except* Errors. Click Ok. Now, all the
cells that contain the #NUM! error are selected. Just
clear those cells.

Biff

 

[Harlan Grove]

More on the previous spreadsheet where I now have:
1. a list of student names in Column A of sheet 1
2. a list of the colleges that they applied to in Columns B through H
in sheet 1
3. a list of colleges in Column A of sheet 2

I now need to fill column B of sheet 2 with the names of all the
students who applied to each college. E.G.:

Sheet 1
Fred Harvard Amherst UCONN UNH
John Yale Bates UNH UCLA
Jeff McGill Yale UVM MIT
Stan Yale Harvard UNH McGill

I'll assume the tabel above is named Tbl1.

Sheet 2
Amherst Fred
Bates John
Harvard Fred, Stan
McGill Jeff, Stan
MIT Jeff
UCLA John
...

I know how to concatenate cells and I know how to count the instances
of each college, but I don't know how to retrieve the names of the
student from the rows where the college name occurs on Sheet 1...

....

Excel doesn't have a generalized concatenation function, only the pair-wise
& operator and the (pointless) work-alike CONCATENATE function. If you want
your name results in one cell, you'll need to use an add-in like Laurent
Longre's MOREFUNC.XLL, which is freely available at

http://longre.free.fr/english/

It provides a function called MCONCAT which may be used in formulas like the
following, which returns the result for Amherst (which is in cell A11 in my
test worksheet).

=SUBSTITUTE(TRIM(MCONCAT(IF(MMULT(--(A11=OFFSET(Tbl1,0,1,,
COLUMNS(Tbl1)-1)),ROW(INDIRECT("1:"&(COLUMNS(Tbl1)-1)))),
INDEX(Tbl1,0,1),"")," "))," ",", ")

 

[jefhal]

Have the politician step to the side,
Ask the educator to take a seat,
Leave the rope across the gate for the businessman,
Offer the sportsman a brief diversion, but please,
make a broad path for the awesome genius and generosity of the on-lin
guru who delivers beauty without recompense and solutions san-souci!

I am routinely astounded by the kindness of strangers encountered o
the web in fora like this.

Thank you. It worked