Formatting Date and adding the contents of 2 rows together

[Unexecel]

Hello

I am trying to no avail to format the date in my workbook to read in the following format DD/MM/YY

At the moment it reads 30-Apr-2014

(See attached sheet) I need to change the date format of all the entries in Column I

Also I would like to add together the contents of cells M, N and P to the contents of other M,N and P to create one row only if they share the same number in column C

(Some of my transactions have been split up and I need to amalgamate them together and column C is their Invoice number)

Here is the code I have so far

Sub Step2()
'
' Step2 Macro
' Step 2 Westminster Purchase Invoice Import
'

'
Cells.Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("A1:A253") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("C1:C253") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("A1:S253")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveCell.Replace What:="Del/inv ", Replacement:="INV", LookAt:=xlWhole _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Invoice ", Replacement:="INV", LookAt:=xlWhole _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="Ret/Crd ", Replacement:="CRN", LookAt:=xlWhole _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

 

[Unexecel]

Thank you in advance for any help you can give

 

[Sathish KV]

Hi,

The colum I is not in date format, use Text to Columns to convert it into proper date format

1) Alt + D and E
2) Next
3) Next
4) In current screen select Date and DMY

 

[Sathish KV]

Use a pivot table for your 2nd requirement

 

[Unexecel]

HI Thanks for this but the net result is still the same 24-apr-2014? although in the formula line it does show the date as being in the correct format DD/MM/YY

I was hoping to use code to automate this process rather then use a manual pivot table excercise

Thank you for your help

 

[Sathish KV]

Hi,

1)Select the whole column
2)Right click and select format
3) In the screen select custom
4) In Type enter dd/mm/yy and hit ok

 

[Unexecel]

hey it is still giving me letters????

 

[Sathish KV]

Could you upload you file now

 

[Unexecel]

Hey i have already uploaded the file it is in my first post

 

[Unexecel]

step 2

 

[Sathish KV]

Hi,

Upload the latest file in which you have made the above mentioned changes