Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sort Data based on date

Posted on 2011-03-14
15
Medium Priority
?
187 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Cartillo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 35131963
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?
0
 

Author Comment

by:Cartillo
ID: 35134050
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.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35137316
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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Cartillo
ID: 35140325
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.
0
 

Author Comment

by:Cartillo
ID: 35140481
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).Replace what:="pcdn-tr/ ", replacement:=""

Hope you can assist

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35140516
Cartillo,

You could add this line, before the other replace line:

Range("C2").Resize(n).Replace what:="pcdn-trQ/ ", replacement:=""
0
 

Author Comment

by:Cartillo
ID: 35140589
Hi StephenJR,

I have tried but both prefix are still exist.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35140640
Cartillo - when I tried it on the sample with the first replacement, that worked.
0
 

Author Comment

by:Cartillo
ID: 35140815
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.
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

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35141528
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

Open in new window

0
 

Author Comment

by:Cartillo
ID: 35141744
Hi StephenJR,

Now the data are line up perfectly, but both prefix still exist.  
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35141778
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?
0
 

Author Comment

by:Cartillo
ID: 35141913
Hi StephenJR,

Removing remove anything before (and including) "/" would be the best option. Hope you help me to create this.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 35142163
OK, try this:
Sub SortData()

Dim i As Long, n As Long, r As Range

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

For Each r In Range("C7").Resize(n).SpecialCells(xlCellTypeConstants)
    r = Trim(Split(r, "/")(1))
Next r

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

Open in new window

0
 

Author Closing Comment

by:Cartillo
ID: 35142207
Cool! Thanks a lot for the great help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question