The searching part where it matches date and store name to take that invoice number and move it to the bottom is where I have no clue at all
I'd "fix" the CSV. This does some sorting and if date and store for a credit ("C" in col 7) match to an invoice then it makes the credit invoice number to that number
Capture.JPG
Then you can do whatever you want with the fixed CSV data
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
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?
If .Cells(i, 3).Value = .Cells(i - 1, 3).Value Then ' Same Store
If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then ' Same Date
Cells(i, 2).Value = .Cells(i - 1, 2).Value ' make invoice same
End If
End If
End If
Next i
End With
ThisWorkbook.Activate
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("Data").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = "Data"
rCSV.Copy ThisWorkbook.Worksheets("Data").Cells(1, 1)
wbCSV.Close False
Application.ScreenUpdating = False
End Sub