With your data in the range A1:B4...

Enter this array formula** in C1 and copy across until you get blanks:

=IF(COLUMNS($C1:C1)SUM($B1:$B4),"",INDEX($A1:$A4, MIN(IF(SUBTOTAL(9,OFFSET($B1,,,ROW(A1:A4)-MIN(ROW(A1:A4))+1))=COLUMNS($C1:C1),ROW(A1:A4)-MIN(ROW(A1:A4))+1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"fischerjp" wrote in message
...
I am created an advanced gradebook program for excel and was wondering if
anyone had an idea of a better way to accomplish the following
Column2 Column2
a 3
b 2
c 5
d 10
etc... with the output being in one row
a a a b b c c c c c d d d d d d d d d d
The idea is to have the teacher type the subject and how many times it
appears as a heading but after the 4th I have to many if functions nested.
I
am using:
if b2=1,a2 for the first return and =IF(B2=2,A2,IF(B2=1,A3)) for the
second, =IF(B2=3,A2,IF(B2=2,A3,IF(AND(B2=1,B3=2),A3,A4)) ) for the third
etc
but I cannot go as far as I want to with this. If anyone has an idea
please
let me know. Thanks