Listing Projects based on Manager Name

P

Pritesh

I have huge data containing running project details, wherein I need to
list-out Names of Projects under various Managers. So a manager can have 1 or
2 or 10 projects under him.

Pls suggest me solution/formula based on below output requirement.

Example Input:

A B
1 Mngr1 Proj1
2 Mngr2 Proj3
3 Mngr1 Proj2
4 Mngr1 Proj5
5 Mngr2 Proj4


Example- Reqd Output:

A B
1 Mngr1 Proj1
2 Mngr1 Proj2
3 Mngr1 Proj5
4
5 Mngr2 Proj3
6 Mngr2 Proj4
7


Regards,
Pritesh
 
M

Mike H

Hi,

The simplest way is to filter the data on column A to display manager 1 and
the assigned projects.

Mike
 
M

Mattlynn via OfficeKB.com

Pritsh
A pivot table could sort and arrange this for you and could be
updated/refreshed as each manager took on more projects etc, or if more
managers were to get involved.
Regards
Matt
 
M

Ms-Exl-Learner

I suggest you to do it in Pivot Table, because it can be done easily by using
Pivot Table in less than a minute time.
 
P

Pritesh

No I can't do that, the data keeps changing, its in thousands of rows having
multiple records which keeps repeating.
 
J

Jacob Skaria

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Sub SortandInsert()

Dim lngLastRow As Long, lngRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A1").Resize(lngLastRow, 2).Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, _
Orientation:=xlTopToBottom

For lngRow = lngLastRow To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then _
Rows(lngRow).Insert
Next

End Sub


If this post helps click Yes
 
P

Pritesh

No... Pivot Table, Data Filters, VBA Macros are less suitable for my task. I
need automated list generation, without Refreshing (Pivot) or manually
sorting (Filter) or clicking button or keys (Macros).

Please suggest me some formula.
 
M

Mike H

Pritesh,

So lets be clear, the following are not suitable or desirable

Filtering
Pivot Table
VB
Clicking buttons.

This seriously limits options, Try this abd drag down

=IF(A2="Mngr1",A2 & " " & B2,"")

Mike
 
M

Mattlynn via OfficeKB.com

I totally agree Ms-Exl Learner !


Ms-Exl-Learner said:
I suggest you to do it in Pivot Table, because it can be done easily by using
Pivot Table in less than a minute time.

--------------------
(MS-Exl-Learner)
--------------------
I have huge data containing running project details, wherein I need to
list-out Names of Projects under various Managers. So a manager can have 1 or
[quoted text clipped - 24 lines]
Regards,
Pritesh
 
P

Pritesh

This formula does not exclude/chk repetitive ones, and I don't need any
merged outputs.


Regards,
Pritesh
 
M

Mattlynn via OfficeKB.com

I still think to summarise the info in a pivot and then to copy the pivot and
paste and values to create a list that should look like what you are after.
Alternatively, in a separte sheet in the same workbook, link the cells so as
you refresh the pivot the list in the new sheet grows accordinbly as a semi-
automated list.
This method will may take a few minutes to set out, but will then be ready in
seconds to use for your purposes.
Regards
Matt


This formula does not exclude/chk repetitive ones, and I don't need any
merged outputs.


Regards,
Pritesh
[quoted text clipped - 52 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top