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

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

That's a tough one for a formula, also one of our formula gurus may provide
one. In the meantime, perhaps a VBA solution will do? This code goes into
that worksheet's code module: right-click on the sheet's name tab and choose
[View Code] from the list. Copy the code below and paste it into the module
presented to you. When you make any change in column A or B of the sheet,
the list in row 1 will be completely rebuilt, empty cells in column A will be
ignored, and 0 or negative values in column B will likewise be ignored.

Private Sub Worksheet_Change(ByVal Target As Range)
'monitor changes in columns A and B and
'alter contents of row 1 beginning at column D
'based on the results of those changes
Const startCellAddress = "D1"
Dim baseCell As Range
Dim colOffset As Integer
Dim listRange As Range
Dim anyEntry As Range
Dim LC As Integer

If Target.Column 2 Then
'no change in A or B
Exit Sub
End If
'prepare to do some work
Set baseCell = Range(startCellAddress)
Application.ScreenUpdating = False
'clear any previous entries
Range(Cells(1, 4), Cells(1, Columns.Count)).ClearContents
'get list of used entries in column A
Set listRange = Range("A1:" & _
Range("A" & Rows.Count).End(xlUp).Address)
For Each anyEntry In listRange
'need an entry in column A and a
'positive value in column B
If Not IsEmpty(anyEntry) And _
anyEntry.Offset(0, 1) 0 Then
For LC = 1 To anyEntry.Offset(0, 1)
baseCell.Offset(0, colOffset) = anyEntry
colOffset = colOffset + 1
Next
End If
Next
'housekeeping
Set baseCell = Nothing
Set listRange = Nothing
End Sub


"fischerjp" wrote:

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


.

how about
Another solution without using array formulas - you will need a helper
column to add up the number of occurrences of each €œlesson€� below - (on my
worksheet this range starts in cell a3)

1 a 3
4 b 2
6 c 5
11 d 10

Then the following equation
=INDEX($B$3:$B$6,MATCH(COLUMN(A1),$A$3:$A$6))
or
=VLOOKUP(COLUMN(A1),$A$3:$B$6,2)

--
Wag more, bark less


"JLatham" wrote:

That's a tough one for a formula, also one of our formula gurus may provide
one. In the meantime, perhaps a VBA solution will do? This code goes into
that worksheet's code module: right-click on the sheet's name tab and choose
[View Code] from the list. Copy the code below and paste it into the module
presented to you. When you make any change in column A or B of the sheet,
the list in row 1 will be completely rebuilt, empty cells in column A will be
ignored, and 0 or negative values in column B will likewise be ignored.

Private Sub Worksheet_Change(ByVal Target As Range)
'monitor changes in columns A and B and
'alter contents of row 1 beginning at column D
'based on the results of those changes
Const startCellAddress = "D1"
Dim baseCell As Range
Dim colOffset As Integer
Dim listRange As Range
Dim anyEntry As Range
Dim LC As Integer

If Target.Column 2 Then
'no change in A or B
Exit Sub
End If
'prepare to do some work
Set baseCell = Range(startCellAddress)
Application.ScreenUpdating = False
'clear any previous entries
Range(Cells(1, 4), Cells(1, Columns.Count)).ClearContents
'get list of used entries in column A
Set listRange = Range("A1:" & _
Range("A" & Rows.Count).End(xlUp).Address)
For Each anyEntry In listRange
'need an entry in column A and a
'positive value in column B
If Not IsEmpty(anyEntry) And _
anyEntry.Offset(0, 1) 0 Then
For LC = 1 To anyEntry.Offset(0, 1)
baseCell.Offset(0, colOffset) = anyEntry
colOffset = colOffset + 1
Next
End If
Next
'housekeeping
Set baseCell = Nothing
Set listRange = Nothing
End Sub


"fischerjp" wrote:

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

I realized that I have the same response twice - I was just trying to find a
way to post the answer on the forum. Trying to post on the forum has been
very erratic today.
--
Wag more, bark less


"Brad" wrote:

Another solution without using array formulas - you will need a helper column
to add up the number of occurrences of each €œlesson€� below - (on my worksheet
this range starts in cell a3)

1 a 3
4 b 2
6 c 5
11 d 10

Then the following equation
=INDEX($B$3:$B$6,MATCH(COLUMN(A1),$A$3:$A$6))
or
=VLOOKUP(COLUMN(A1),$A$3:$B$6,2)

--
Wag more, bark less


"fischerjp" wrote:

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

Another solution without using array formulas - you will need a helper column
to add up the number of occurrences of each €œlesson€� below - (on my worksheet
this range starts in cell a3)

1 a 3
4 b 2
6 c 5
11 d 10

Then the following equation
=INDEX($B$3:$B$6,MATCH(COLUMN(A1),$A$3:$A$6))
or
=VLOOKUP(COLUMN(A1),$A$3:$B$6,2)

--
Wag more, bark less


"fischerjp" wrote:

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