Sorting data

Sub M()
Dim a, b, c As Double
c = 0
b = Range("A1048576").End(xlUp).Row
For a = b To 2 Step -1
Range("D" & a).Select
If Range("D" & a).Value <> Range("D" & a - 1).Value Then
c = c + 1
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Workbooks.Add
Range("A1").Value = "Transaction Status"
Range("B1").Value = "Transaction ID"
Range("C1").Value = "Transaction Date"
Range("D1").Value = "RD Reference"
Range("E1").Value = "Amount"

Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:="C:\Users\sense soft\Documents\Book" & c, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
End If
Next a
End Sub

Logaraj Sekar
Steel Contributor
May 09, 2018
Hi Laura,

Based on the RD reference, i created macro. So it will count no of RD references (without duplication).

If RD reference count is 30, it will create 30 Sheets. In case you need change, explain it i will do changes in macro.
- LauraDaisy
  Copper Contributor
  May 10, 2018
  Hi Logaraj Sekar
  
  I think there may have been some confusion in what I'm trying to do.
  
  I have a data set of 600 lines of transaction data with dates and references. In that, 100 are duplicate reference numbers but have different transaction dates. All the rest are unique reference numbers.
  
  I need to move the data so that one csv. file doesn't contain the same reference number.
  
  When I do it manually, I ended up with 3 csv. files - 1 ) of 500 lines, 2 ) of 63 lines, 3) of 37 lines.
  
  Then I can import in bulk.
  
  Does that make sense?
  
  if it can be done it would be awesome as manually is quite time consuming and requires several checks to ensure no mistake on my part.
  
  Thanks :)
  - Logaraj Sekar
    Steel Contributor
    May 10, 2018
    Hi LauraDaisy
    
    You are telling that, of which 600 lines, you moving them to 3 CSV files.
    
    500 Lines in one CSV, 63 Lines in another CSV and remaining in another CSV.
    
    I think i forget to write code for sorting in Macro. So that it creates 30 files instead of 3.
    
    Please sort it manually and run macro (or) add these lines after third line in Macro. It will automatically sort the data.
    
    Note: Here "Sheet1" refers name of the sheet. Change it to your sheet name
    
    Range("A1:E" & b).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D" & b), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E" & b)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

Forum Discussion