Excel 2013 updating balances daily

[TRex112817]

I have a very large excel 2013 file that sorts and subtotals lines by social security number. I set it up to have all account balances that were submitted to an outside agency. I must update this outside agency daily to reduce their balance by the amount of payments per account we received. I can download a daily payment report from our main system. Can I upload this payment report by account to a column in my main report to the specific accounts paid?

social sec # acct begin bal paid 11/17/17 paid 11/25/17 balance

xxx-xx-xxxx 123456 70.00 -25.00 -45.00 0.00
xxx-xx-xxxx 159267 130.00 -30.00 100.00

bbb-bb-bbbb 287659 200.00 -100.00 100.00

ccc-cc-cccc 301006 95.00 -95.00 0.00
ccc-cc-cccc 309269 110.00 110.00
ccc-cc-cccc 286914 265.00 -45.00 220.00

So, one upload would be the paid 11/17/17 and another would be 11/25/17. How can I get the paid amounts to land on the correct account number lines? I've been doing it by hand, but there has to be an easier way.
THANKS!!!!

 

[AFPathfinder]

Is the download a text file with fixed width or delimited values? Being able to create multiple rows out of a single text line might be difficult to do outside VBA. How familiar are you with VBA/macros?

 

[TRex112817]

Is the download a text file with fixed width or delimited values? Being able to create multiple rows out of a single text line might be difficult to do outside VBA. How familiar are you with VBA/macros?

The download is a fixed width text file. Not familiar at all with VBA/macros. BUT, if it's possible to simplify all of the data entry I do, I'll learn it!

 

[AFPathfinder]

If you find yourself dealing with a lot of repetitive Excel work in your job, VBA may be a huge tool for you. I had a job that had many weekly text files that needed to be molded into a traditional tables. I nearly macro'd myself right out of that job.

The first step I usually take when working on a macro for fixed width text files is to map out where the columns are. I'll change the font into a mono-spaced font like Courier New and start charting how many characters are in each column. For instance, the SSN field would be 9 characters (11 if dashes are included). The next field looks like it would be 7 characters (one space followed by 6 digits).

For the dollar figures, knowing the start of the field is important as there is a start difference between 5.00 and 5000.00. I usually start the next column on the first space after the cents of the previous amount. I'll use a couple cell functions to help me out like FIND (returns character positions) and LEN (returns total count of characters in a string). For instance, the first split line in your first post has "-45.00" in the string. "-45" only occurs once in that string so I'd use the FIND function to return the character number it starts on: "=FIND(A1, "-45").

In the macro, you'll need a loop for sure to loop through each row in the text file.

'The variables that will be used are identified here.
Dim i As Integer
Dim finalRow As Integer

'This is a common line that is used to identify the last row in the dataset.
finalRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To finalRow
    'more code here
Next i

The more important part, though, is the stuff inside the loop that works each of those rows. If you're used to IF statements in cell formulas, it should be rather easy to grasp them in VBA.

If Len(Cells(i, 1).Value) = 74 Then
    'If the previous line is true, code placed here will run
ElseIf Len(Cells(i, 1).Value) = 112 Then
    "If the first If was false, but the ElseIf was true, this code will run
Else
    "If both were false, this plain Else is a catchall for all other situations
End If

There are a good arrangement of functions that are useful in parsing strings. Altogether, it would start looking like this:

Sub TexttoTable()
    'The variables that will be used are identified here.
    Dim i As Integer
    Dim finalRow As Integer
    Dim ssn As String
    Dim accNum As String
    
    'This is a common line that is used to identify the last row in the dataset.
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = finalRow To 2 Step -1
        'The SSN and account number are going to be placed on each row, so add them to variables to be used more than once
        ssn = Replace(Left(Cells(i, 1).Value, 11), "-", "")
        accNum = Trim(Mid(Cells(i, 1).Value, 12, 7))
        
        'If the row only contains one payment.
        If Len(Cells(i, 1).Value) = 32 Then
            Cells(i, 2).Value = accNum
            Cells(i, 3).Value = Trim(Mid(Cells(i, 1).Value, 18, 7))
            Cells(i, 4).Value = Trim(Mid(Cells(i, 1).Value, 25, 7))
            
            'This one last as it overwrites the cell with all the data in it.
            Cells(i, 1).Value = ssn
            
        'If the row contains more than one payment.
        ElseIf Len(Cells(i, 1).Value) = 46 Then
            Cells(i, 2).Value = accNum
            Cells(i, 3).Value = Trim(Mid(Cells(i, 1).Value, 18, 7))
            Cells(i, 4).Value = Trim(Mid(Cells(i, 1).Value, 25, 7))
            
            'Insert extra row below the current row being parsed and input the values
            Range("A" & i + 1).EntireRow.Insert
            Cells(i + 1, 1).Value = ssn
            Cells(i + 1, 2).Value = accNum
            Cells(i + 1, 3).Value = Trim(Mid(Cells(i, 1).Value, 32, 7))
            Cells(i + 1, 4).Value = Trim(Mid(Cells(i, 1).Value, 39, 7))
            
            'This one last as it overwrites the cell with all the data in it.
            Cells(i, 1).Value = ssn
        
        'I usually have a statement in the Else part that deletes out unwanted rows; headers/footers and such.
        Else
            Range("A" & i).EntireRow.Delete
            
        End If
    Next i
End Sub

 

[TRex112817]

Looks like I have some learning to do! Thank you for pointing me in the right direction.

 

[AFPathfinder]

You're welcome! I would have suggested sending me the text file, but the PII shouldn't be shared over commercial internet (and not with that unknown weirdo AFPathfinder). If you run into any snags, just let us know.