Cartillo
asked on
Sort Data based on date
Hi Experts,
I would like to request Experts help create a macro to delete all prefix stars with “pcdn-tr/” at Column C and sort the data ascendant (A to Z) with condition the priority given to the nearest start date (Column H). Some header (Column C) has multiple start date at column C, for that type of data the first date become a point of reference (the multiple start date need to be maintained with the Header (Column C). Hope Experts will help me to create this feature.
FilterData.xls
I would like to request Experts help create a macro to delete all prefix stars with “pcdn-tr/” at Column C and sort the data ascendant (A to Z) with condition the priority given to the nearest start date (Column H). Some header (Column C) has multiple start date at column C, for that type of data the first date become a point of reference (the multiple start date need to be maintained with the Header (Column C). Hope Experts will help me to create this feature.
FilterData.xls
Cartillo - if I were going to do this, I would remove the blank rows, and replicate the column C entry for each column H entry. Is that acceptable?
ASKER
StephenJR,
Instead of copying column H data, is that possible copy column C data. E.g. at row 45 we have "Euro: Euro's Monarchies - generic :15 - Tomorrow 10/9pm". We can copy this data at row 46 to 48. We can use the same "Header" for blank row at Column C as long as the row has Start Date.
Instead of copying column H data, is that possible copy column C data. E.g. at row 45 we have "Euro: Euro's Monarchies - generic :15 - Tomorrow 10/9pm". We can copy this data at row 46 to 48. We can use the same "Header" for blank row at Column C as long as the row has Start Date.
Cartillo - if I have understood correctly, try this:
Sub x()
Dim i As Long, n As Long
On Error Resume Next
Columns("H:H").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
n = Range("H" & Rows.Count).End(xlUp).Row
Range("C2").Resize(n).Replace what:="pcdn-tr/ ", replacement:=""
For i = 1 To 3
With Range("A1:C" & n).Columns(i)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
Next i
With Range("A1:I" & n)
.Sort key1:=.Cells(1, 8), order1:=xlAscending, header:=xlYes
End With
End Sub
ASKER
Hi StephenJR,
I need your help. After test with the actual data I realize that we need to copy data from A to G if the data at column C "Header" has multiple "Start Date". Hope you can help me to add this function.
I need your help. After test with the actual data I realize that we need to copy data from A to G if the data at column C "Header" has multiple "Start Date". Hope you can help me to add this function.
ASKER
Hi StephenJR,
Managed to solve above issue by modifying this line,
With Range("A1:C" & n).Columns(i)
but I'm not sure how to add an additional "prefix" for deletion. How to add this prefix for deletion ""pcdn-trQ/ " besides "pcdn-tr/ " at this line:
Range("C2").Resize(n).Repl ace what:="pcdn-tr/ ", replacement:=""
Hope you can assist
Managed to solve above issue by modifying this line,
With Range("A1:C" & n).Columns(i)
but I'm not sure how to add an additional "prefix" for deletion. How to add this prefix for deletion ""pcdn-trQ/ " besides "pcdn-tr/ " at this line:
Range("C2").Resize(n).Repl
Hope you can assist
Cartillo,
You could add this line, before the other replace line:
Range("C2").Resize(n).Repl ace what:="pcdn-trQ/ ", replacement:=""
You could add this line, before the other replace line:
Range("C2").Resize(n).Repl
ASKER
Hi StephenJR,
I have tried but both prefix are still exist.
I have tried but both prefix are still exist.
Cartillo - when I tried it on the sample with the first replacement, that worked.
ASKER
Hi StephenJR,
I made some changes at the row ( I need to reserve first few rows), dose this has any impact with the prefix deletion? Attached the code for your perusal. I could make a blunder in this code.
I made some changes at the row ( I need to reserve first few rows), dose this has any impact with the prefix deletion? Attached the code for your perusal. I could make a blunder in this code.
Sub SortData()
Dim i As Long, n As Long
On Error Resume Next
Columns("H7:H").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
n = Range("H" & Rows.Count).End(xlUp).Row
Range("C7").Resize(n).Replace what:="pcdn-trQ/ ", replacement:=""
Range("C7").Resize(n).Replace what:="pcdn-tr/", replacement:=""
For i = 1 To 3
With Range("A7:G" & n).Columns(i)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
Next i
With Range("A7:I" & n)
.Sort key1:=.Cells(1, 8), order1:=xlAscending, Header:=xlYes
End With
End Sub
What about this?
Sub SortData()
Dim i As Long, n As Long
On Error Resume Next
Range("H7", Range("H" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
n = Range("H" & Rows.Count).End(xlUp).Row
Range("C7").Resize(n).Replace what:="pcdn-trQ/ ", replacement:=""
Range("C7").Resize(n).Replace what:="pcdn-tr/", replacement:=""
For i = 1 To 3
With Range("A7:G" & n).Columns(i)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
Next i
With Range("A7:I" & n)
.Sort key1:=.Cells(1, 8), order1:=xlAscending, Header:=xlYes
End With
End Sub
ASKER
Hi StephenJR,
Now the data are line up perfectly, but both prefix still exist.
Now the data are line up perfectly, but both prefix still exist.
Not for me. A few of these "thcn-prx/" are still there but the others disappear. Would it be easier to remove anything before (and including) "/" or could you have "/" elsewhere?
ASKER
Hi StephenJR,
Removing remove anything before (and including) "/" would be the best option. Hope you help me to create this.
Removing remove anything before (and including) "/" would be the best option. Hope you help me to create this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool! Thanks a lot for the great help