|
If function limitations help
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 |
If function limitations help
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 |
If function limitations help
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 |
If function limitations help
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 . |
If function limitations help
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 . |
If function limitations help
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 |
If function limitations help
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 |
If function limitations help
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 |
| All times are GMT +1. The time now is 03:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com