List Unique Occurences and Count

[Guest]

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 Beban]

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

 

[Guest]

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

 

[Domenic]

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!

 

[Alan Beban]

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

 

[Domenic]

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..."

 

[Guest]

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