If function limitations help

  • Thread starter Thread starter fischerjp
  • Start date Start date
F

fischerjp

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.
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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.
 
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)
 
Back
Top