hi, this is my data (theres 707 lines in total)Transaction StatusTransaction IDTransaction DateRD ReferenceAmountDeclined - Bank declined1001/11/201711966420.00Declined - Bank declined1101/12/201711966420.00Declined - Bank declined1201/01/201811966420.00Declined - Bank declined1301/02/201811966420.00Declined - Bank declined1401/03/201811965820.00Declined - Bank declined1501/04/201811965820.00Declined - Insufficient funds1601/09/201711965820.00Declined - Insufficient funds1701/10/201711965820.00Declined - Insufficient funds1801/09/201711966520.00Declined - Insufficient funds1901/09/201711966620.00Declined - Insufficient funds2007/09/201711973020.00Declined - Insufficient funds2107/10/201711973020.00Declined - Insufficient funds2207/11/201711973020.00Declined - Insufficient funds2307/12/201711972520.00Declined - Insufficient funds2407/01/201811972520.00Declined - Insufficient funds2507/02/201811972520.00Declined - Insufficient funds2607/03/201811972520.00Declined - Insufficient funds2728/09/201711973720.00 i need to sort it for a CSV file import. Each CSV file cannot have duplicate references - is there a quick way to do this. Fingers crossed as I'm currently having to manually move each reference to new worksheet.

So I tried this (thankyou v.much :) Logaraj Sekar ) I tried it on a new set of data - it worked but pulled out 30 CSV files. When I did it manually there are only 2 spreadsheets. Any ideas? I dont know anything about VBA code so not sure where to start. Thanks:)

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.

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 :)

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).SelectActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D" & b), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormalWith ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:E" & b) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .ApplyEnd With

LauraDaisy

Copper Contributor

May 01, 2018

Sorting data

hi, this is my data (theres 707 lines in total)

Transaction Status	Transaction ID	Transaction Date	RD Reference	Amount
Declined - Bank declined	10	01/11/2017	119664	20.00
Declined - Bank declined	11	01/12/2017	119664	20.00
Declined - Bank declined	12	01/01/2018	119664	20.00
Declined - Bank declined	13	01/02/2018	119664	20.00
Declined - Bank declined	14	01/03/2018	119658	20.00
Declined - Bank declined	15	01/04/2018	119658	20.00
Declined - Insufficient funds	16	01/09/2017	119658	20.00
Declined - Insufficient funds	17	01/10/2017	119658	20.00
Declined - Insufficient funds	18	01/09/2017	119665	20.00
Declined - Insufficient funds	19	01/09/2017	119666	20.00
Declined - Insufficient funds	20	07/09/2017	119730	20.00
Declined - Insufficient funds	21	07/10/2017	119730	20.00
Declined - Insufficient funds	22	07/11/2017	119730	20.00
Declined - Insufficient funds	23	07/12/2017	119725	20.00
Declined - Insufficient funds	24	07/01/2018	119725	20.00
Declined - Insufficient funds	25	07/02/2018	119725	20.00
Declined - Insufficient funds	26	07/03/2018	119725	20.00
Declined - Insufficient funds	27	28/09/2017	119737	20.00

i need to sort it for a CSV file import.

Each CSV file cannot have duplicate references - is there a quick way to do this.

Fingers crossed as I'm currently having to manually move each reference to new worksheet.

excel

Macros & VBA

13 Replies

Logaraj Sekar

Iron Contributor

May 02, 2018

Hi LauraDaisy

I almost done. Minor correction is required.

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

LauraDaisy
Copper Contributor
May 09, 2018
So I tried this (thankyou v.much :) Logaraj Sekar )

I tried it on a new set of data - it worked but pulled out 30 CSV files.

When I did it manually there are only 2 spreadsheets.

Any ideas? I dont know anything about VBA code so not sure where to start.

Thanks
:)
- Logaraj Sekar
  Iron Contributor
  May 10, 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.

Forum Discussion

Sorting data

13 Replies

Resources