# How can I generate this list ? **URL:** \ **Category:** Factual Questions\ **Created:** [August 18, 2005, 11:40pm UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679 "2005-08-18T23:40:21Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [August 18, 2005, 11:40pm UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/1 "2005-08-18T23:40:21Z")
I have a list like this: John :3 Tom :4 Mike :5 and so on, a list of names followed by a number. What I need to do is generate a list of names that repeat the same number of times as the digit after them, so the list above would look like this: John John JOhn Tom Tom Tom Tom Mike Mike MIke Mike Mike and so on. Right now it’s in an Excel sheet, but it seems to me there’s a way to do this with Perl if I export it to text, but I haven’t been able to figure out how. Or is there an easier way to do it with Excel? Or Access? Or Something? ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [August 18, 2005, 11:50pm UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/2 "2005-08-18T23:50:28Z")
I rigged up a macro that seems to work in excel VBA This will take its input from the first column of the worksheet and write output into the second column. Once it finds a blank in column A it’ll quit. ```auto Public Sub macro1() Dim row1 As Integer, row2 As Integer, v As Variant, c As String, k As Integer Dim j As Integer row1 = 1 row2 = 1 Do c = Sheet1.Cells(row1, 1) If c = "" Then Exit Sub v = Split(c, ":") If UBound(v) > 0 Then k = v(1) For j = 1 To k Sheet1.Cells(row2, 2) = v(0) row2 = row2 + 1 Next row2 = row2 + 1 End If row1 = row1 + 1 Loop End Sub ``` (It should skip over and ignore anything without a colon in it.) ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [August 19, 2005, 12:16am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/3 "2005-08-19T00:16:40Z")
Cool. I’ll give it a try thanks ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [August 19, 2005, 12:34am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/4 "2005-08-19T00:34:12Z")
do I need to modify this at all to get it to run? I copied and pasted it into a new macro, but nothing happens when I run it. I don’t even get an error, but nothing happens at all. ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [August 19, 2005, 12:37am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/5 "2005-08-19T00:37:04Z")
> [@batsto](#): > > do I need to modify this at all to get it to run? I copied and pasted it into a new macro, but nothing happens when I run it. I don’t even get an error, but nothing happens at all. Hmm… is your worksheet called sheet1? Maybe that makes a difference. Is your input right at the very top of the first column of the sheet? If cell a1 is blank it would quit immediately. Aside from that, I can’t think of what would me going wrong. 😕 ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [August 19, 2005, 12:44am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/6 "2005-08-19T00:44:53Z")
It is called Sheet1, and my data starts in the very first cel. It doesn’t do what macros usually do when I write them incorrectly, like throw up an error message. But nothing happens. At all. ( I didn’t even assign a keyboard shortcut, I’m just going to Tools\>Macros and running it from there) ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [August 19, 2005, 12:55am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/7 "2005-08-19T00:55:23Z")
Effortlessly easy in FileMaker ```auto Set Field [GlobalText Field A, ""] Loop If [batso's field <> GlobalText Field A] Set Field [GlobalText Field A, batso'sfield] Set Field [GlobalNumber Field A, Middle(GlobalText Field A, Position(GlobalText Field A, ":", 1, 1)+1, Length(GlobalText Field A))] Set Field [GlobalNumber Field B, 1] Loop Exit Loop If [GlobalNumber Field B > GlobalNumber Field A] New Record Set Field [Name Field, Middle(GlobalText Field A, 1, Position(GlobalText Field A, ":", 1, 1)-1)] Set Field [GlobalNumber Field B, GlobalNumber Field B + 1] End Loop End Loop ``` ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [August 19, 2005, 1:14am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/8 "2005-08-19T01:14:15Z")
Except I don’t have Filemaker, so that won’t help me. I’ve never used it before…is it the sort of thing I would find useful, or can I do most of the same stuff with other programs? ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [August 19, 2005, 1:18am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/9 "2005-08-19T01:18:05Z")
I got it to work! For some reason when I save it to my Pesonal Macro Workbook, it doesn’t work. But when I save it as a macro in the workbook I plan to use it in, it runs just fine. Thanks for the help! ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [August 19, 2005, 11:32am UTC](https://boards.straightdope.com/t/how-can-i-generate-this-list/317679/10 "2005-08-19T11:32:22Z")
> [@batsto](#): > > I got it to work! For some reason when I save it to my Pesonal Macro Workbook, it doesn’t work. But when I save it as a macro in the workbook I plan to use it in, it runs just fine. Thanks for the help! Oh, that could explain it actually. When you save it in the personal macro workbook, it would expect to find the data in the personal macro workbook. PMW macros usually need to have some slightly fancier code to hook into activeWorksheet or some such. Sorry 'bout that.