How can I generate this list ?

Factual Questions
1

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?

2

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.



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

3

Cool. I’ll give it a try

thanks

4

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.

5

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. :confused:

6

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)

7

Effortlessly easy in FileMaker



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
8

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?

9

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!

10

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.