Here's one play which automates it using non-array formulas ..
A sample construct is available at:
http://www.savefile.com/files/6028021
AutoCopyData_ByInitialsCol_To_RespNameSheet_NonArray.xls
In sheet: All (the "master")
Assume data in cols A to E, data in row2 down,
with the key col = col D ("Initials"),
where: S, M, K, P, etc would be listed
List the initials across in K1:N1 (S, M, P, K)
Put in K2: =IF($D2=K$1,ROW(),"")
Copy across to N2, fill down to say, N20
to cover the max expected extent of data
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: Scott
With the same col headers pasted into A1:E1
Put in A2:
=IF(ISERROR(SMALL(OFFSET(All!$J:$J,,MATCH(LEFT(WSN,1),All!$K$1:$N$1,0)),ROWS
($A$1:A1))),"",INDEX(All!A:A,MATCH(SMALL(OFFSET(All!$J:$J,,MATCH(LEFT(WSN,1)
,All!$K$1:$N$1,0)),ROWS($A$1:A1)),OFFSET(All!$J:$J,,MATCH(LEFT(WSN,1),All!$K
$1:$N$1,0)),0)))
Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any initial.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)
Cols A to E will return only the lines for the initial: S from "All",
with all lines neatly bunched at the top
Now, just make a copy of the sheet: Scott, rename it as: Mark
and we'd get the results for initial: M
Repeat the copy > rename sheet process
to get the rest of the initials sheets (a one-time job)
Adapt to suit ..
