Any help with this one would be greatly appreciated ... even if the answer is
that it can't be done :)

Scenario:

1) the names of 15 executives are listed in Column E
2) the names of associates are in Column F

The associates form a labor pool from which the executives can assign
projects. Each row of the tab named 'Summary Report' contains an executive
name (column E) and associate name (column F) and project information. There
are multiple occurrences of both executive and associate rows and no
necessary correlations between the two.

So, on a separate tab my boss would like to see a list of executive names
with a count of the associates they are using, then a list of those
associates.

Eckard 3 Martin, Smith, Williams
Franks 6 Robertson, Hoi, Jones, Smith, Martin, Wenston
Liu 5 Williams, Hoi, Young, Hansen, Robertson

In my manual process, I move down the column of executive names, and find
the first occurrence of the associates name. My second step is to count the
number of associate's names found for each executive.

As this is likely to be a weekly report, I am keen to automate the process.

Thank you,

PGiessler

Does your description mean that for Executive Eckard in Column E, for
example, Associate Maritn, for example, might appear more than once in
Column F?

Alan Beban

PGiessler wrote:
Any help with this one would be greatly appreciated ... even if the answer is
that it can't be done :)

Scenario:

1) the names of 15 executives are listed in Column E
2) the names of associates are in Column F

The associates form a labor pool from which the executives can assign
projects. Each row of the tab named 'Summary Report' contains an executive
name (column E) and associate name (column F) and project information. There
are multiple occurrences of both executive and associate rows and no
necessary correlations between the two.

So, on a separate tab my boss would like to see a list of executive names
with a count of the associates they are using, then a list of those
associates.

Eckard 3 Martin, Smith, Williams
Franks 6 Robertson, Hoi, Jones, Smith, Martin, Wenston
Liu 5 Williams, Hoi, Young, Hansen, Robertson

In my manual process, I move down the column of executive names, and find
the first occurrence of the associates name. My second step is to count the
number of associate's names found for each executive.

As this is likely to be a weekly report, I am keen to automate the process.

Thank you,

PGiessler

Alan,

Thanks for the reply. Yes, both appear multiple times in their respective
columns as follows:

Column E Column F

Eckard Martin
Eckard Smith
Franks Robertson
Eckard Williams
Franks Hoi
Liu Williams
Franks Jones
Franks Smith
Liu Hoi
Liu Young
Franks Martin
Liu Hansen
Franks Wenston
Liu Robertson


Hopefully this helps clarify the issue I am trying to tackle.

Thanks Again,

PGiessler


"Alan Beban" wrote:

Does your description mean that for Executive Eckard in Column E, for
example, Associate Maritn, for example, might appear more than once in
Column F?

Alan Beban

PGiessler wrote:
Any help with this one would be greatly appreciated ... even if the answer is
that it can't be done :)

Scenario:

1) the names of 15 executives are listed in Column E
2) the names of associates are in Column F

The associates form a labor pool from which the executives can assign
projects. Each row of the tab named 'Summary Report' contains an executive
name (column E) and associate name (column F) and project information. There
are multiple occurrences of both executive and associate rows and no
necessary correlations between the two.

So, on a separate tab my boss would like to see a list of executive names
with a count of the associates they are using, then a list of those
associates.

Eckard 3 Martin, Smith, Williams
Franks 6 Robertson, Hoi, Jones, Smith, Martin, Wenston
Liu 5 Williams, Hoi, Young, Hansen, Robertson

In my manual process, I move down the column of executive names, and find
the first occurrence of the associates name. My second step is to count the
number of associate's names found for each executive.

As this is likely to be a weekly report, I am keen to automate the process.

Thank you,

PGiessler

So, if I understand you correctly, Eckard and Martin could have another
association relating to another project. If so, try the following which
involves the use of two helper columns that can be hidden...

G1, copied down:

=E1&F1

H1, copied down:

=IF(COUNTIF($G$1:G1,G1)=1,1,0)

Then, enter a unique list of executives in Column I.

J1, copied down:

=SUMIF($E$1:$E$14,$I1,$H$1:$H$14)

K1, copied across and down:

=IF(COLUMNS($K1:K1)<=$J1,INDEX($F$1:$F$14,SMALL(IF (($E$1:$E$14=$I1)*($H$1
:$H$14=1),ROW($F$1:$F$14)-ROW($F$1)+1),COLUMNS($K1:K1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
PGiessler wrote:

Alan,

Thanks for the reply. Yes, both appear multiple times in their respective
columns as follows:

Column E Column F

Eckard Martin
Eckard Smith
Franks Robertson
Eckard Williams
Franks Hoi
Liu Williams
Franks Jones
Franks Smith
Liu Hoi
Liu Young
Franks Martin
Liu Hansen
Franks Wenston
Liu Robertson


Hopefully this helps clarify the issue I am trying to tackle.

Thanks Again,

PGiessler


"Alan Beban" wrote:

Does your description mean that for Executive Eckard in Column E, for
example, Associate Maritn, for example, might appear more than once in
Column F?

Alan Beban

PGiessler wrote:
Any help with this one would be greatly appreciated ... even if the
answer is
that it can't be done :)

Scenario:

1) the names of 15 executives are listed in Column E
2) the names of associates are in Column F

The associates form a labor pool from which the executives can assign
projects. Each row of the tab named 'Summary Report' contains an
executive
name (column E) and associate name (column F) and project information.
There
are multiple occurrences of both executive and associate rows and no
necessary correlations between the two.

So, on a separate tab my boss would like to see a list of executive names
with a count of the associates they are using, then a list of those
associates.

Eckard 3 Martin, Smith, Williams
Franks 6 Robertson, Hoi, Jones, Smith, Martin, Wenston
Liu 5 Williams, Hoi, Young, Hansen, Robertson

In my manual process, I move down the column of executive names, and find
the first occurrence of the associates name. My second step is to count
the
number of associate's names found for each executive.

As this is likely to be a weekly report, I am keen to automate the
process.

Thank you,

PGiessler

Domenic wrote:
So, if I understand you correctly, Eckard and Martin could have another
association relating to another project. . . .

That may be what he meant, but it's not what his illustration shows; the
illustration suggests that he misunderstood my question.

Alan Beban

Alan,

Thanks for the reply. Yes, both appear multiple times in their respective
columns as follows:

Column E Column F

Eckard Martin
Eckard Smith
Franks Robertson
Eckard Williams
Franks Hoi
Liu Williams
Franks Jones
Franks Smith
Liu Hoi
Liu Young
Franks Martin
Liu Hansen
Franks Wenston
Liu Robertson


Hopefully this helps clarify the issue I am trying to tackle.

Thanks Again,

PGiessler


"Alan Beban" wrote:


Does your description mean that for Executive Eckard in Column E, for
example, Associate Maritn, for example, might appear more than once in
Column F?

Alan Beban

In article ,
Alan Beban wrote:

That may be what he meant, but it's not what his illustration shows; the
illustration suggests that he misunderstood my question.

Yes, agreed. I went based on what he said, "...both appear multiple
times..." :)

Domenic & Alan;

Thank you for your responses. I was off-network yesterday so I will be
trying the formula today. From the looks of it, it appears to be what I need.
I will post later to verify.

Thanks again guys!

PGiessler

"Domenic" wrote:

In article ,
Alan Beban wrote:

That may be what he meant, but it's not what his illustration shows; the
illustration suggests that he misunderstood my question.

Yes, agreed. I went based on what he said, "...both appear multiple
times..." :)