Convert Many Invoice Formats to a Standard Format

[Paul_Hossler]

Third try. See if this works for you

test1.csv is the input you provided a long time ago, and test1-out.csv has a 9 in front of the invoice for matching stores and dates

Option Explicit

Sub FixCSV()
    Dim sCSV As String
    Dim wbCSV As Workbook
    Dim wsCSV As Worksheet
    Dim rCSV As Range, rCSV1 As Range
    Dim i As Long, j As Long    
    sCSV = Application.GetOpenFilename("ERP File, *.CSV")
    If sCSV = "False" Then Exit Sub
    Application.ScreenUpdating = False    
    Workbooks.Open Filename:=sCSV    
    Set wbCSV = ActiveWorkbook
    Set wsCSV = ActiveSheet    
    With wsCSV      '   Guessing
        .Cells(1, 1).Value = "Date"
        .Cells(1, 2).Value = "Invoice"
        .Cells(1, 3).Value = "Store"
        .Cells(1, 4).Value = "Product"
        .Cells(1, 5).Value = "Qty"
        .Cells(1, 6).Value = "Cost"
        .Cells(1, 7).Value = "InvCred"
        .Cells(1, 8).Value = "Something"    
        Set rCSV = .Cells(1, 1).CurrentRegion
        Set rCSV1 = rCSV.Cells(2, 1).Resize(rCSV.Rows.Count - 1, rCSV.Columns.Count)        
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=rCSV1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=rCSV1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SortFields.Add Key:=rCSV1.Columns(7), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal        
            .SetRange rCSV
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With   
        .Cells(2, 1).Select
    End With        
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True    
    With rCSV
        For i = 2 To .Rows.Count
            If .Cells(i, 7).Value = "C" Then    '  CREDIT?
                j = i
                'same store and same date
                Do While (.Cells(j, 3).Value = .Cells(i - 1, 3).Value) And _
                    (.Cells(j, 1).Value = .Cells(i - 1, 1).Value)
                    .Cells(j, 2).Value = "'9" & .Cells(i - 1, 2).Value   '   add leading 9
                    .Cells(j, 7).Value = "-C"    '  add marker
                    j = j + 1
                Loop
            End If
        Next i    
        Call .Columns(7).Replace("-C", "C", xlWhole)
    End With    
    i = InStrRev(wbCSV.Name, ".")
    sCSV = wbCSV.Path & Application.PathSeparator & Left(wbCSV.Name, i - 1) & "-out.csv"    
    Application.DisplayAlerts = False
    On Error Resume Next
    Kill sCSV
    On Error GoTo 0
    Application.DisplayAlerts = True    
    wbCSV.SaveAs sCSV, xlCSV
    wbCSV.Close False    
    Application.ScreenUpdating = False    
    MsgBox "CSV Converted as " & sCSV    
End Sub