VLOOKUP ( I think )

Posted by Mike on February 21, 2001 8:08 AM

Situation:
Lets say I have a table with two columns headings - StaffName and ProjectName. for example:

StaffName ProjectName
Joe Design Project
Sally Enginering Project
Frank Design Project
Joe Engineering Project
Sally Design Project

Now, I want to develop a table that lists the StaffName only once and the multiple projects in the 2nd 3rd 4th or nth column. What I think I'm after is the VLOOKUP ability to return the 2nd 3rd 4th or nth occurence of a criter in the array:

StaffName ProjectName1 ProjectName2
Joe Design Project Engineering Project
Sally Design Project Engineering Project
Frank Design Project

Posted by Mike on February 21, 2001 8:31 AM

Posted by Faster on February 21, 2001 8:34 AM

Add a third column like, project value or
project date. Then use a pivot table (Data/Pivot Table Report...)

StaffName ProjectName Value
Joe Design Project 500.00
Sally Enginering Project 75.00
Frank Design Project 69.00
Joe Engineering Project 85.00
Sally Design Project 400.00

In the pivot table wizard use StaffName and Project
Name for the ROW. And use Value for the DATA.

This returns:

StaffName ProjectName Total
Frank Design Project 69
Joe Design Project 500
Engineering Project 85
Sally Design Project 400
Enginering Project 75
Grand Total 1129

The Pivot Table is great for reporting.

hope this helps

Posted by Mike on February 21, 2001 12:36 PM

Thanks,
You're close, but no cigar...

I messed with the Pivot Table, but i need the Project Names in consecutive columns (not rows).

What I was hoping to find was a way to get the VLOOKUP function to deliver me not just the 1st occurence of the criteria in an arrauy, but the Nth occurence (or whatever occurence I specified).

I'm still looking for the answer... anyone else?

Posted by Aladin Akyurek on February 21, 2001 1:03 PM

OK, you can get a formula-based solution thru e-mail if you promise to come back here and explain how it's done. :-)

Aladin

Posted by Mike on February 21, 2001 1:52 PM

If it works, I'll do my best. This one has really stumped me and I thought I knew what I was doing. Thanks.

Mike

Posted by Aladin Akyurek on February 21, 2001 2:09 PM

: Thanks, : You're close, but no cigar...

The mail I've sent to you has been returned undelivered! Drop me a line so that I can reply with an attachment.

Cheers.

Aladin

Posted by Mike on February 22, 2001 5:29 AM

Aladin: Please try either meveges@accessdc.com or meveges@computer.org (note that this one is .org)
Thanks again.

Mike