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