Manager Summaries

[John Ortt]

Hi everyone,

I have a single sheet with a list of manager's names and their employees in
the folowing format

Manager Employee
Joe Bloggs Fred Flintstone
Bill Gates Goeff Capes
Bill Gates Barney Rubble
Joe Bloggs Dan Dare

I would like a separate sheet for each manager which just pulls out their
employees, ie:

Manager Employee
Joe Bloggs Fred Flintstone
Joe Bloggs Dan Dare

I can do it but only with blank gaps where the other managers names go. Is
it possible using cell formulas to get them all to be displayed on
consecutive lines?

Hope that makes sense and thx in advance.

John

 

[Max]

One play ..

Sample construct at:
http://cjoint.com/?lslBHYRH7Y
AutoSortData_BySheetName.xls

Assume the source list is in sheet: Master
in cols A to B, headers in row1, data from row2 down

Using empty cols to the right of the data, say cols K onwards

List the Mgr's names in K1, L1 across: Joe Bloggs, Bill Gatling, etc

Put in K2: =IF($A2=K$1,ROW(),"")
Copy K2 across to L2, fill down to say L10,
to cover the max expected data in the master list

Click Insert > Name > Define

Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan)

In a new sheet named: Joe Bloggs
With the same col headers in A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$1:$IV$1,0)),ROWS(
$A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Ma
ster!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$
1:$IV$1,0)),0)))

Copy A2 across to B2, fill down to B10
(cover the same range size as was done in "Master"' cols K, L)

Cols A to B will return only the lines for the mgr: Joe Bloggs from
"Master", all neatly bunched at the top

Now, just make a copy of the sheet: Joe Bloggs, rename it as: Bill Gatling,
and you'd get the results for Bill Gatling. Repeat as required to get the
lists for all the other Mgrs.