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

Awesome, as usual Biff ... The OP posted the same question over in the new
Microsoft Answers forum at:
http://social.answers.microsoft.com/...9-495465da4eb9
so you may want to post the solution there also.

"T. Valko" wrote:

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


.

Thnaks, Jerry!

Did a copy/paste to Answers.

--
Biff
Microsoft Excel MVP


"JLatham" wrote in message
...
Awesome, as usual Biff ... The OP posted the same question over in the new
Microsoft Answers forum at:
http://social.answers.microsoft.com/...9-495465da4eb9
so you may want to post the solution there also.

"T. Valko" wrote:

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


.