Longest Call, Longest Call Phone Number and Longest Call Date

drewmorris

New Member
Joined
Oct 2, 2018
Messages
2
Hi and thank you for all help in advance,

I have a csv. file that I download and it gives me the data like this (not real data):

A B C D
[TABLE="width: 399"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CallDate[/TD]
[TD]DialledNumber[/TD]
[TD]CallingNumber[/TD]
[TD]CallDuration[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2018 10:34[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07333333333[/TD]
[TD="align: right"]00:04:47[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018 12:22[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07222222222[/TD]
[TD="align: right"]00:06:04[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018 11:50[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07111111111[/TD]
[TD="align: right"]00:06:13[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2018 16:01[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07444444444[/TD]
[TD="align: right"]00:04:06[/TD]
[/TR]
[TR]
[TD="align: right"]17/09/2018 12:43[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07555555555[/TD]
[TD="align: right"]00:19:59[/TD]
[/TR]
</tbody>[/TABLE]


If possible, I would like a couple of Formulas which will give me the following:

[TABLE="width: 468"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD]Longest Call [/TD]
[TD]Longest Caller Number[/TD]
[TD]Longest Call Date[/TD]
[/TR]
[TR]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]00:00:00
[/TD]
[TD]XXXXX XXX XXX[/TD]
[TD]XX/XX/XX
[/TD]
[/TR]
</tbody>[/TABLE]
01999999999

For the 'Longest Call' I have tried using the following formula: {=MAX(IF(criteria_range=criteria,value_range))} but it has been giving me '00:00:00' even when there is data.

In summary, I would like a formula for extracting the longest call, longest call number and the date the call was done.

Again thank you for all help.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to the MrExcel board!

I suspect that you problems might be stemming from data that appears to be numerical but is actually text. Do some investigation with, for example, =ISNUMBER(D2)
Same for column A.

Otherwise, if column D is numerical, your formula works for me, though I have given an alternative in column E that doesn't require the array formula entry.

Excel Workbook
ABCDE
1CallDateDialledNumberCallingNumberCallDuration
227/09/2018 10:3401888888888073333333330:04:47
326/09/2018 12:2201999999999072222222220:06:04
426/09/2018 11:5001999999999071111111110:06:13
519/09/2018 16:0101888888888074444444440:04:06
617/09/2018 12:4301888888888075555555550:19:59
7
8
9NumberLongest CallLongest Caller NumberLongest Call Date
10018888888880:19:590755555555517/09/20180:19:59
11019999999990:06:130711111111126/09/20180:06:13
Calls



One other note:
It would theoretically be possible for a given number in column A in the lower section to have more than one row at the top with equal longest call duration. That would mean more than one possible value for columns C & D for that value.
These formulas will only return one value. Do you need to allow for this possibility & if so, how would you want the multiple results presented?

What version of Excel are you using?
 
Upvote 0
With PowerQuery aka Get&Transform

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"DialledNumber"}, {{"Count", each _, type table}, {"Max", each List.Max([CallDuration]), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Max", type time}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Count"})
in
    #"Removed Columns"[/SIZE]
and
Code:
[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Max"},#"Table1 (2)",{"CallDuration"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"CallDate", "CallingNumber"}, {"CallDate", "CallingNumber"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table1",{{"CallDate", type datetime}})
in
    #"Changed Type"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]CallDate[/td][td=bgcolor:#5B9BD5]DialledNumber[/td][td=bgcolor:#5B9BD5]CallingNumber[/td][td=bgcolor:#5B9BD5]CallDuration[/td][td][/td][td=bgcolor:#70AD47]DialledNumber[/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]CallDate[/td][td=bgcolor:#70AD47]CallingNumber[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
27/09/2018 10:34​
[/td][td=bgcolor:#DDEBF7]
1888888888​
[/td][td=bgcolor:#DDEBF7]
7333333333​
[/td][td=bgcolor:#DDEBF7]
00:04:47​
[/td][td][/td][td=bgcolor:#E2EFDA]
1999999999​
[/td][td=bgcolor:#E2EFDA]
00:06:13​
[/td][td=bgcolor:#E2EFDA]
26/09/2018 11:50​
[/td][td=bgcolor:#E2EFDA]
7111111111​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
26/09/2018 12:22​
[/td][td]
1999999999​
[/td][td]
7222222222​
[/td][td]
00:06:04​
[/td][td][/td][td]
1888888888​
[/td][td]
00:19:59​
[/td][td]
17/09/2018 12:43​
[/td][td]
7555555555​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
26/09/2018 11:50​
[/td][td=bgcolor:#DDEBF7]
1999999999​
[/td][td=bgcolor:#DDEBF7]
7111111111​
[/td][td=bgcolor:#DDEBF7]
00:06:13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
19/09/2018 16:01​
[/td][td]
1888888888​
[/td][td]
7444444444​
[/td][td]
00:04:06​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
17/09/2018 12:43​
[/td][td=bgcolor:#DDEBF7]
1888888888​
[/td][td=bgcolor:#DDEBF7]
7555555555​
[/td][td=bgcolor:#DDEBF7]
00:19:59​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
Welcome to the MrExcel board!

I suspect that you problems might be stemming from data that appears to be numerical but is actually text. Do some investigation with, for example, =ISNUMBER(D2)
Same for column A.

Otherwise, if column D is numerical, your formula works for me, though I have given an alternative in column E that doesn't require the array formula entry.

Calls

ABCDE
NumberLongest Caller NumberLongest Call Date

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:121px;"><col style="width:104px;"><col style="width:154px;"><col style="width:124px;"><col style="width:76px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]CallDate[/TD]
[TD="align: right"]DialledNumber[/TD]
[TD="align: right"]CallingNumber[/TD]
[TD="align: right"]CallDuration[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]27/09/2018 10:34[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07333333333[/TD]
[TD="align: right"]0:04:47[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]26/09/2018 12:22[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07222222222[/TD]
[TD="align: right"]0:06:04[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]26/09/2018 11:50[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07111111111[/TD]
[TD="align: right"]0:06:13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]19/09/2018 16:01[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07444444444[/TD]
[TD="align: right"]0:04:06[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]17/09/2018 12:43[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07555555555[/TD]
[TD="align: right"]0:19:59[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]Longest Call[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]0:19:59[/TD]
[TD="align: right"]07555555555[/TD]
[TD="align: right"]17/09/2018[/TD]
[TD="align: right"]0:19:59[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]0:06:13[/TD]
[TD="align: right"]07111111111[/TD]
[TD="align: right"]26/09/2018[/TD]
[TD="align: right"]0:06:13[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B10{=MAX(IF(B$2:B$6=A10,D2:D6))}
C10=INDEX(C$2:C$6,AGGREGATE(15,6,(ROW(C$2:C$6)-ROW(C$2)+1)/((B$2:B$6=A10)*(D$2:D$6=B10)),1))
D10=INT(INDEX(A$2:A$6,AGGREGATE(15,6,(ROW(C$2:C$6)-ROW(C$2)+1)/((B$2:B$6=A10)*(D$2:D$6=B10)),1)))
E10=AGGREGATE(14,6,D$2:D$6/(B$2:B$6=A10),1)

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


One other note:
It would theoretically be possible for a given number in column A in the lower section to have more than one row at the top with equal longest call duration. That would mean more than one possible value for columns C & D for that value.
These formulas will only return one value. Do you need to allow for this possibility & if so, how would you want the multiple results presented?

What version of Excel are you using?


This has helped me immensely! Thank you so much for the time you spent. I cannot express how grateful I am.

As a first experience of using this website, I will not be able to recommend you enough to all.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top