Biff,

I'd use a VBA function to do this, something like what follows. Pass in with

absolute cell references the range that has your data names (your "Data1",

"Data2" range), and the range that contains the CountNumbers. Something

like

=FillOutFromValues($A$1:$A$5,$B$1:$B$5)

Array enter that formula in to a range containing at least SUM(CountNumbers)

rows. If the formula is entered in to more than SUM(CountNumbers) rows, the

last elements of the array are set to vbNullStrings and will show up as

blanks in the worksheet. If the formula is entered into less than

SUM(CountNumbers) rows, elements at the end of the array are not displayed.

Function FillOutFromValues(DataNameRange As Range, _

CountRange As Range) As Variant

Dim ResultArr() As Variant

Dim ResultRowCount As Long

Dim Ndx As Long

Dim CountNdx As Long

Dim ResultNdx As Long

If DataNameRange.Columns.Count > 1 Then

FillOutFromValues = CVErr(xlErrRef)

Exit Function

End If

If CountRange.Columns.Count > 1 Then

FillOutFromValues = CVErr(xlErrRef)

Exit Function

End If

If DataNameRange.Rows.Count <> CountRange.Rows.Count Then

FillOutFromValues = CVErr(xlErrRef)

Exit Function

End If

On Error Resume Next

If IsObject(Application.Caller) = False Then

FillOutFromValues = CVErr(xlErrRef)

Exit Function

End If

ResultRowCount = Application.WorksheetFunction.Max( _

Application.WorksheetFunction.Sum(CountRange), _

Application.Caller.Rows.Count)

ReDim ResultArr(1 To ResultRowCount)

ResultNdx = 0

For Ndx = 1 To DataNameRange.Rows.Count

For CountNdx = 1 To CountRange.Cells(Ndx, 1)

ResultNdx = ResultNdx + 1

ResultArr(ResultNdx) = DataNameRange.Cells(Ndx)

Next CountNdx

Next Ndx

For ResultNdx = ResultNdx + 1 To ResultRowCount

ResultArr(ResultNdx) = vbNullString

Next ResultNdx

FillOutFromValues = Application.Transpose(ResultArr)

End Function

--

Cordially,

Chip Pearson

Microsoft MVP - Excel

Pearson Software Consulting, LLC

www.cpearson.com
(email address is on the web site)