Excel scenario help

[Corsair 115]

I asked for some Excel help here before, and the resident Excel gurus were wonderfully helpful. So, I thought I'd try again. Here's a situation that seems like might be a challenge to sort out (it is for me certainly!). :)

I have a column with several rows of unique text entries in it. In another column I want to duplicate each of those unique rows of text from the first column a specified number of times in sequence. Below is an example of what I mean. The left column shows the starting point, and the right column is what I want the result to look like.
Atlanta Atlanta
Boston Atlanta
Chicago Atlanta
Boston
Boston
Boston
Chicago
Chicago
ChicagoIn the example, each row entry from the first column is being duplicated three times in the second column in the same order as the first column. Any solution needs to be flexible since the starting column may have any number of starting rows, and the number of duplications in the result column might be any number.

As far as I can tell, there's no direct formula answer to this. It looks like it's going to require some intermediary steps. But what those steps are I have no idea. I'm stumped. Any and all help appreciated...

[sphenisc]

Not sure what version of Excel you're using; this is for 2003. It'll be something similar in other versions.

Select menu items Tools > Macro > Visual Basic Editor

Then on the new window Insert > Module

Paste in the text below


Sub duplist()

ActiveSheet.Range("A1").Select

dup = ActiveSheet.Range("C1")
currow = 1
Do
For t = 1 To dup
ActiveSheet.Cells(currow, 2) = ActiveWindow.ActiveCell
currow = currow + 1
Next
ActiveWindow.ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell <> ""

End Sub


Close the Visual Basic Window and you should be back in Excel.

Type the list in column A (no gaps), and the number of duplicates required in C1 (I'm assuming they're all duplicated the same number of times.

When it's set up

Tools > Macro > Macros


Select duplist from the list, and then Run

[sphenisc]

Sorry - much simpler solution

Enter this formula in B1

=IF(INDIRECT(ADDRESS(INT((ROW()-1)/C$1)+1,1))<>0,INDIRECT(ADDRESS(INT((ROW()-1)/C$1)+1,1)),"")

Fill down for as many lines as you're ever likely to need.
Enter the number of duplicates in C1

[Corsair 115]

Thanks, sphenisc! Excellent stuff.