Vlookup to create a list from a larger list

I have a report with all our active projects that I can export into Excel.
The project number is in column A and the Project Manager is in column F,
what I would like to do on another worksheet is list one project manager and
all his projects. As the original report is in project number order, one
project manager is scattered through out the report, can I create a list of
one manger's projects on another sheet without the blank lines in between
each project and without having to sort...so my results looks like the
following??

A(PM) B(Proj #)
Joe Smith 65485
66895
72589
89542
I would like one worksheet for each Manager without having to recreate each
week

Thank you very much,

Terri

In a helper column of your Projects sheet (suppose this is column M),
put this formula in M2 and copy down:

=F2&"_"&COUNTIF(F$2:F2,F2)

This will give you a unique sequential count for each Project Manager.
(I've assumed that you have a header row in row 1). Then in Joe
Smith's sheet, with his name in A2, you can put this formula in B2:

=IF(ISNA(MATCH(A$2&_"&ROW(B1),Projects!M:M,0)),"", INDEX(Projects!
A:A,MATCH(A$2&"_"&ROW(B1),Projects!M:M,0)))

Copy this down column B as far as you feel is necessary (i.e. to cover
the maximum number of projects that you think a project manager is
likely to have).

Then you can copy Joe Smith's sheet for as many times as you have
Project Managers, and rename each sheet in turn as well as changing
the entry in A2 of each sheet.

Hope this helps.

Pete



On Dec 9, 10:55*pm, Terri wrote:
I have a report with all our active projects that I can export into Excel.. *
The project number is in column A and the Project Manager is in column F,
what I would like to do on another worksheet is list one project manager and
all his projects. *As the original report is in project number order, one
project manager is scattered through out the report, can I create a list of
one manger's projects on another sheet without the blank lines in between
each project and without having to sort...so my results looks like the
following??

* *A(PM) * * * * * *B(Proj #)
Joe Smith * * * * 65485
* * * * * * * * * * * *66895
* * * * * * * * * * * *72589
* * * * * * * * * * * *89542
I would like one worksheet for each Manager without having to recreate each
week

Thank you very much,

Terri

Works like magic, thank you soooooo much

Terri

"Pete_UK" wrote:

In a helper column of your Projects sheet (suppose this is column M),
put this formula in M2 and copy down:

=F2&"_"&COUNTIF(F$2:F2,F2)

This will give you a unique sequential count for each Project Manager.
(I've assumed that you have a header row in row 1). Then in Joe
Smith's sheet, with his name in A2, you can put this formula in B2:

=IF(ISNA(MATCH(A$2&_"&ROW(B1),Projects!M:M,0)),"", INDEX(Projects!
A:A,MATCH(A$2&"_"&ROW(B1),Projects!M:M,0)))

Copy this down column B as far as you feel is necessary (i.e. to cover
the maximum number of projects that you think a project manager is
likely to have).

Then you can copy Joe Smith's sheet for as many times as you have
Project Managers, and rename each sheet in turn as well as changing
the entry in A2 of each sheet.

Hope this helps.

Pete



On Dec 9, 10:55 pm, Terri wrote:
I have a report with all our active projects that I can export into Excel..
The project number is in column A and the Project Manager is in column F,
what I would like to do on another worksheet is list one project manager and
all his projects. As the original report is in project number order, one
project manager is scattered through out the report, can I create a list of
one manger's projects on another sheet without the blank lines in between
each project and without having to sort...so my results looks like the
following??

A(PM) B(Proj #)
Joe Smith 65485
66895
72589
89542
I would like one worksheet for each Manager without having to recreate each
week

Thank you very much,

Terri

You're welcome, Terri - thanks for feeding back.

Pete

On Dec 10, 4:28*pm, Terri wrote:
Works like magic, thank you soooooo much

Terri