group column and sum column

[oracle_coorg]

hi
i need to calculate for particular call number and total the duration
eg:sheet2 cell A callnumber cell b total duration
eg:
9242867683 6,126.00
9845351502 9,347.00
9880746961 2,437.00

[anandbohra]

sort the data as per call number then
use subtotal (data-subtotal)
as every change in call number
count duration

[oracle_coorg]

hi
if i can post this here
this this the query in oracle which i need
>select CALLED_NUMBER,sum(DURATION)DURATION from tele
group by CALLED_NUMBER
order by duration
i need this macros

[anandbohra]

what exactly u want??

do u want your excel data to be shows as total duration for the particular call
or u want query in Oracle
or u want to extract data from oracle into excel.

pl clarify

& also attach excel file (not zip ) with example so that we can understood your exact problem

[oracle_coorg]

hi
i have attached /posted the result in the attachement in sheet2
i need a macro in excels to do this ,from sheet1 to sheet2
:help

[anandbohra]

pl refer attached file

this one is with out put

just wait for the macro

[oracle_coorg]

thanxs ya i needed this order by duration could be better
once agin thanxs a lot anand

[anandbohra]

here is the macro for the same

Sub sum_if_call()
Sheets("Sheet1").Select
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"H1"), Unique:=True
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Sumif Anands Result").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
Selection.NumberFormat = "0"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Sum of Duration"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF(Sheet1!C3,'Sumif Anands Result'!RC[-1],Sheet1!C4)"
Range("B2").Select
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select

ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
End Sub

[oracle_coorg]

but this was done through the tool how about a macro

[anandbohra]

do u want the macro for subtotal also???

here it is

Sub subtotal_macro()
Sheets("Sheet1").Select
Range("a1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select
End Sub

[oracle_coorg]

hi
am i troubling u
its working fine
but can i ask u without all those etc sheets or subtotal
just from sheet1 cant i get the ouput in sheet2 with order by duration
by using macro...

[anandbohra]

no way
the more genuinely u trouble someone the more knowledge increases of both

ok now explain me what exactly u want after this process.

if u want to simply see the data order by call duration
run this code after sum_if_call code

Sub order_by_duration()
Sheets("Sumif Anands Result").Select
Cells.Select
Range("A1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub


the above code first copy all formula & paste with thier values.
this will sort the data in ascending order as per duration

[oracle_coorg]

thx.. but unable to sort asc... or order by duration

Selection.sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

[oracle_coorg]

hi
ATTACHED THE editted .xls
pls look into