Here's the construct write-up for the earlier implemented sample ..

One play which automates it using non-array formulas ..

In sheet: x (the "master")
Assume source data is in cols A to F, data in row2 down,
with the key col = col F (Class)

Note that you'd need to change any class names with "/" to say, underscore,
eg: V 11_12 Boys
This is because the "/" is a prohibited character in sheetnames

Put in G2: =IF(F2="","",IF(COUNTIF(F$2:F2,F2)1,"",ROW()))
Copy down to cover the max expected extent of source data. Leave G1 blank
This col flags unique items (the Classes) with arb row numbers, which will
be read by the formula in H1 across

Put in H1:
=IF(COLUMNS($A:A)COUNT($G:$G),"",INDEX($F:$F,SMAL L($G:$G,COLUMNS($A:A))))
Copy H1 across as far as required to cover the max expected number of unique
Classes
In H1 across will be extracted all the unique Classes neatly bunched to the
left

Put in H2: =IF(OR($F2="",H$1=""),"",IF($F2=H$1,ROW(),""))
Copy H2 across/fill down

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.

Then, in a new sheet named after one of the Classes, eg: V 9A
With the same col headers pasted into A1:F1

Put in A2:
=IF(ROWS($1:1)COUNT(OFFSET(x!$G:$G,,MATCH(WSN,x!$ H$1:$IV$1,0))),"",
INDEX(x!A:A,MATCH(SMALL(OFFSET(x!$G:$G,,MATCH(WSN, x!$H$1:$IV$1,0)),ROWS($1:1)),OFFSET(x!$G:$G,,MATCH (WSN,x!$H$1:$IV$1,0)),0)))
Copy A2 across to F2, fill down to say F50, to cover the max expected extent
for any Class. Cols A to F will return only the lines for the class: V 9A
from the mastersheet; x, with all lines neatly bunched at the top. Now, just
dress the sheet: V 9A up as desired, then make copies of it, rename each
copy as the other Classes: Pre-Nursery, A 8C, etc and you'd get the
corresponding results for each of those Classes. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---