CODEWITHSUNDEEP
vbaexcel
user2296381
user2296381

Reputation: 197

Best Way to Create Import Files with Excel?

Not sure what the best way to describe this situation is, but I'll do my best. I am trying to create an import file for a database system using Excel.

Here is what we have as an example:

1) Names in a Column
2) Accounts in a Column (Accounts 1-3)
3) Amounts (An Amount for each Combo, ex: Name1, Account1, Name 1, Account2)

So what I want is an easy way (possibly with VBA?) to create an import file similar to below:

(Columns A, B, C)
Name#1, Account#1, Amount#1
Name#1, Account#2, Amount#2
Name#1, Account#3, Amount#3
Name#2, Account#1, Amount#4
Name#2, Account#2, Amount#5
etc.. etc..

Is there anyway to do this without having to do a ton of copying and pasting? I tried Pivot Tables, but it just doesn't seem to work for my situation

Sample Data:

Names     |  Accounts   |   Amounts
David        11230          $32.50
Marry        11240          $2.00
Jerry        54500          $990.00
             64000          $500.00
                            $300.00
                            $600.00
                            $330.55
                            $500.00
                            $45.00
                            $53.38
                            $75.00
                            $44.00

Thus the intended output we want is:

David, 11230, $32.50
David, 11240, $2.00
David, 54500, $990.00
David, 64000, $500.00
Marry, 11230, $300.00
Marry, 11240, $600.00
....Continue...

Hope that helps

Upvotes: 0

Views: 61

Answers (1)

Tim Williams
Tim Williams

Reputation: 166256

This worked for me:

Sub tester()

    Dim sht As Worksheet, rngNames As Range, rngAccts As Range
    Dim nm As Range, acct As Range, i As Long

    Set sht = ActiveSheet

    With sht
        Set rngNames = .Range(.Range("A2"), _
                          .Cells(.Rows.Count, 1).End(xlUp))
        Set rngAccts = .Range(.Range("B2"), _
                          .Cells(.Rows.Count, 2).End(xlUp))
    End With

    i = 1

    For Each nm In rngNames.Cells
    For Each acct In rngAccts.Cells
        i = i + 1
        sht.Cells(i, 5).Value = nm.Value
        sht.Cells(i, 6).Value = acct.Value
        sht.Cells(i, 7).Value = sht.Range("C1").Offset(i - 1, 0).Value
    Next acct
    Next nm

End Sub

Inputs are in ColA-C, output goes to Cols E-G

Upvotes: 1

Related Questions