Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Vlookup to create a list from a larger list

3 views
Skip to first unread message

Terri

unread,
Dec 9, 2008, 5:55:01 PM12/9/08
to

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

Pete_UK

unread,
Dec 9, 2008, 7:42:43 PM12/9/08
to
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

Terri

unread,
Dec 10, 2008, 11:28:06 AM12/10/08
to
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 <Te...@discussions.microsoft.com> wrote:

> > I have a report with all our active projects that I can export into Excel..

Pete_UK

unread,
Dec 10, 2008, 11:38:13 AM12/10/08
to
You're welcome, Terri - thanks for feeding back.

Pete

0 new messages