• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort dynamic excel sheet

I

IKHAN

Member
Hi, I get a dynamic excel sheet everyday downloaded from portal,Before i start working on data have to run throu some steps daily.Can it be customized to click ctrl+x and all below jobs be done.


-I have to DELETE first 3 rows.

-Freeze the header file(ROW 1) and (column A and Column B)

-Highlight sheet and Sort by Column A(contains numbers) in descending order.

-Sort by Column H(header -Start date (format :Apr 04, 2012 5:30:00 AM

)( ascending order)

-Highlight entire rows with matching numbers in column A with same colour ( say yellow)

-Highlight entire row next matching number in cloumn A with different color(say green)

and so on.

- To Check the last column R(header -Comment) and if any comment found will be highlighted in RED (COMPLETE ROWS)


Note: Sheet has header row
 
Ikhan


Could you please upload some sample data and a sample of what it should be like when complete.


I am sure someone will develop some code for this for you.
 
Sample data Received :


ID Tick ID Cus ID Status Type Required Reason Start Finish Des Obj Details iD Impact Service Actual Start Actual Finish Comment

2222 4563 Cus ID Status Type Yes Reason Apr 04, 2012 5:30:00 AM Apr 04, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

3333 2323 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

1111 6589 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish To be rescheduled

3333 4545 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

1111 6878 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish To be rescheduled

2222 5467 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish


Output reqd.


ID Tick ID Cus ID Status Type Required Reason Start Finish Des Obj Details iD Impact Service Actual Start Actual Finish Comment

3333 4545 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

3333 2323 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

2222 4563 Cus ID Status Type Yes Reason Apr 22, 2012 5:30:00 AM Apr 04, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

2222 5467 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish

1111 6878 Cus ID Status Type Yes Reason Apr 22, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish To be rescheduled

1111 6589 Cus ID Status Type Yes Reason Apr 04, 2012 2:00:00 AM Apr 22, 2012 6:00:00 AM Des Obj Details iD Impact Service Actual Start Actual Finish To be rescheduled


Note : output matched numbers in column A should be highlighted with colors(complete row) and if comment found in cloumn H (tO BE HIGHLIGHTED IN RED)
 
Ikhan

Try the following code

Copy it into a Code Module in VBA

On the worksheet with the original data either

Alt F8 and execute the Format_Data routine

or add a Button or Shape to the sheet and link the macro Format_Data to it


It assumes the sheet is called Sheet1

[pre]
Code: 
Sub Format_Data()

Dim LR As Long

'Delete top 3 rows
Rows("1:3").Delete Shift:=xlUp

'Find Last Row No.
LR = Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Sort according to Rules
Range("A1").Select
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SortFields.Clear
.SetRange Range("A1:R" & LR)
.SortFields.Add Key:=Range("A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("H2:H" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'Set freeze panes
Range("B2").Select
ActiveWindow.FreezePanes = True

'Add helper column & hide it
Range("S2").FormulaR1C1 = "=IF(RC[-18]<>R[-1]C[-18],NOT(R[-1]C),R[-1]C)"

Range("S2").AutoFill Destination:=Range("S2:S" & LR)
Columns("S:S").EntireColumn.Hidden = True

'Setup Conditional Formatting
Range("A2:R2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$S2=TRUE"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = True

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$S2=FALSE"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = True

ActiveSheet.Range(Cells(2, 18), Cells(LR, 18)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R2<>"""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Color = -16776961
End With
Selection.FormatConditions(1).StopIfTrue = True

Range("A1").Select

End Sub
[/pre]
 
Hui, Thanks for the code, Iam new to excel , Not sure about this


" Try the following code

Copy it into a Code Module in VBA

On the worksheet with the original data either

Alt F8 and execute the Format_Data routine

or add a Button or Shape to the sheet and link the macro Format_Data to it


It assumes the sheet is called Sheet1 "


If you can please give little more steps on how to..
 
Hui, I was able to run the code.


It does the job perfectly except need to highlight complete Row IN RED if any comment found in Column R "


Currently only column R is curently highlighted in red.


Thanks for the code again.
 
Near end of code, change this line

[pre]
Code: 
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R2<>"""""
to this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$R2<>"""""
[/pre]
 
Hui,


Replaced it , No change - Only column H gets highlighted in RED(IF COMMENTS PRESENTS) dOESN'T highlight entire row in RED.
 
Ah, didn't see that Hui selected a different range as well. Change this line:

[pre]
Code: 
ActiveSheet.Range(Cells(2, 18), Cells(LR, 18)).Select
to this:

ActiveSheet.Range(Cells(2, "A"), Cells(LR, "R")).Select
[/pre]
to apply the formatting to columns A:R.
 
Thanks luke - Now the background color of highlighted row(based on comment section -coulmn H) doesn't work.
 
You must log in or register to reply here.
Back
Top