Forum Discussion
Sorting data - by reference
Hi
I have data set like this;
| Date | Reference |
| 28/11/2017 | 119737 |
| 28/01/2018 | 119737 |
| 28/02/2018 | 119737 |
| 28/03/2018 | 119737 |
| 07/09/2017 | 119787 |
| 07/10/2017 | 119787 |
| 07/11/2017 | 119787 |
| 07/12/2017 | 119787 |
| 07/01/2018 | 119787 |
| 07/02/2018 | 119787 |
| 07/03/2018 | 119787 |
| 28/09/2017 | 119807 |
| 28/11/2017 | 119807 |
| 28/09/2017 | 119839 |
| 28/10/2017 | 119839 |
| 28/11/2017 | 119839 |
| 28/12/2017 | 119839 |
| 28/01/2018 | 119839 |
| 28/02/2018 | 119839 |
| 28/03/2018 | 119839 |
| 01/10/2017 | 119931 |
| 01/11/2017 | 119931 |
| 01/01/2018 | 119931 |
| 01/02/2018 | 119931 |
I need to split the references /dates to different CSV files as one file cant have duplicate references.
Im currently filtering and numbering, then will move all 1's, all 2's etc.
Is there a quicker way to do this?
Thanks :)
8 Replies
Hi,
If i've this problem, I will go for "Remove duplicates" function to removing duplicates.
Then i do macro for filtering and export as CSV.
On which basis you separating CSV? on date basis or reference basis?
Unfortunately all the dates are different so I need all the lines to import into the database.
I just need to split the data so there's only one line for each reference on each page.
To do it for the first lot of data I sorted references and then went down numbering them 1,2,3 etc and then moving all 1's onto a a new tab, all 2's onto a new tab etc.
Sadly my eyes let me down and there were a few references with several dates on one page.
Therefore the import failed.
Does that make sense?
Hi,
So you want reference 119737 in one CSV file, 119807 in another CSV file and for each reference you need each file.
1. Sort values in Reference
2. Use the Macro to split each reference.
Sub Macro1()
Dim a As Double
b = Range("A1048576").End(xlUp).Row
For a = b To 2 Step -1
Range("B" & a).Select
If Range("B" & a).Value <> Range("B" & a - 1).Value Then
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
End If
Next a
End Sub
