Formulate multi results on seperate column

[seaton]

Hi,

I need of some help guys, cant get my head round this one...

Column A has peoples names (eg Steve Smith, Barry Jackson, Terry Tester)
Column B has the number of appointments that each person is responsible for.. (1,3,2)

I need to produce a single column which will display each persons name multiplied by the amount of appointments.

So Column C would look something like this

Steve Smith
Barry Jackson
Barry Jackson
Barry Jackson
Terry Tester
Terry Tester

and so on..

any ideas would be greatly appreciated.

[IanHyman]

You could write a macro to loop and produce names for the number of appointments they have.

[seaton]

Thanks for the help Ian, will give it a go and let you know how I get on shortly

[seaton]

Ian,

Thanks again, the macro suggestion worked great I used the following code for the macro should anyone need it.

Option Explicit
Sub Allocate()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyMore As Long

Set wks = Worksheets("Sheet1")
With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
HowManyMore = .Cells(iRow, "B").Value - 1
If HowManyMore > 0 Then
.Rows(iRow + 1).Resize(HowManyMore).Insert
.Cells(iRow + 1, "A").Resize(HowManyMore, 1).Value _
= .Cells(iRow, "A").Value
.Cells(iRow + 1, "b").Resize(HowManyMore, 1).Value _
= .Cells(iRow, "b").Value
End If
Next iRow

End With
End Sub

[Tony Valko]

Here's a formula method.

List the items in column A and the number of times they are to be repeated in column B:

Please Login or Register  to view this content.

Data in the range A2:B4.

Create this defined named expression...

Name: Array
Refers to: =ROW(INDIRECT("1:3"))
OK

This refers to the number of entries in column A.

Enter this array formula** in D2:

=IF(ROWS(D$2:D2)>SUM(B$2:B$4),"",INDEX(A$2:A$4,MIN(IF(SUBTOTAL(9,OFFSET(B$2,,,Array))>=ROWS(D$2:D2),Array))))

** 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.

Copy down until you get blanks.

Please Login or Register  to view this content.

[seaton]

Hi Tony,

Many thanks for this formula, it worked a treat and suited me better than using the macro as I had to run it on several sheets.

Thanks to you and Ian for the help it was greatly appreciated.

Kind regards,

Mark

[Tony Valko]

You're welcome. Thanks for the feedback!