lookup question

[Guest]

I have two columns. The first column have a listing of people's names and the second column list their project numbers. There are recurring names that have different project numbers. If I make a list of people's names I need a formula that shows all their different project numbers. example

col A col
row 1 billy 74
row 2 sam 45
row 3 cindy 45
row 4 billy 74
row 5 billy 57
row 6 sam 87

I typed in all the different names, but the cell next to their names contains a formula that list all their project numbers. How would I do that

eric

 

[Frank Kabel]

Hi
a solution without formulas: Try using a pivot table for creating such
a report (you'll use a dummy data field like Count of project IDs).
Take a look at the following sites for further info
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html


For a formula approach: As VLOOKUP only return ONE match you'll
probably need VBA for this. One way
- download Alan Beban's array functions (http://home.pacbell.net/beban)
They'll include a function called VLOOKUPS (for returning multiple
lookup results)
- download the free add-in Morefunc.xll as you want to combine the
results in one cell
(http://longre.free.fr/english/)

Now use the following formula:
Assumption: column E contains your names
=MCONCAT(VLOOKUPS(E1,$A$1:$B$100,2,0)," - ")



--
Regards
Frank Kabel
Frankfurt, Germany

I have two columns. The first column have a listing of people's names

and the second column list their project numbers. There are recurring
names that have different project numbers. If I make a list of
people's names I need a formula that shows all their different project
numbers. example:

col A col B
row 1 billy 741
row 2 sam 457
row 3 cindy 452
row 4 billy 747
row 5 billy 578
row 6 sam 879

I typed in all the different names, but the cell next to their names

contains a formula that list all their project numbers. How would I do
that?