Longest Call, Longest Call Phone Number and Longest Call Date

[drewmorris]

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

CallDate	DialledNumber	CallingNumber	CallDuration
27/09/2018 10:34	01888888888	07333333333	00:04:47
26/09/2018 12:22	01999999999	07222222222	00:06:04
26/09/2018 11:50	01999999999	07111111111	00:06:13
19/09/2018 16:01	01888888888	07444444444	00:04:06
17/09/2018 12:43	01888888888	07555555555	00:19:59

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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

A	B	C	D
Number	Longest Call	Longest Caller Number	Longest Call Date
01888888888	00:00:00	XXXXX XXX XXX	XX/XX/XX

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.

 

[Peter_SSs]

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
	A	B	C	D	E
1	CallDate	DialledNumber	CallingNumber	CallDuration
2	27/09/2018 10:34	01888888888	07333333333	0:04:47
3	26/09/2018 12:22	01999999999	07222222222	0:06:04
4	26/09/2018 11:50	01999999999	07111111111	0:06:13
5	19/09/2018 16:01	01888888888	07444444444	0:04:06
6	17/09/2018 12:43	01888888888	07555555555	0:19:59
7
8
9	Number	Longest Call	Longest Caller Number	Longest Call Date
10	01888888888	0:19:59	07555555555	17/09/2018	0:19:59
11	01999999999	0:06:13	07111111111	26/09/2018	0: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?

 

[sandy666]

With PowerQuery aka Get&Transform

[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

[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]

CallDate	DialledNumber	CallingNumber	CallDuration		DialledNumber	Max	CallDate	CallingNumber
27/09/2018 10:34​	1888888888​	7333333333​	00:04:47​		1999999999​	00:06:13​	26/09/2018 11:50​	7111111111​
26/09/2018 12:22​	1999999999​	7222222222​	00:06:04​		1888888888​	00:19:59​	17/09/2018 12:43​	7555555555​
26/09/2018 11:50​	1999999999​	7111111111​	00:06:13​
19/09/2018 16:01​	1888888888​	7444444444​	00:04:06​
17/09/2018 12:43​	1888888888​	7555555555​	00:19:59​

 

[drewmorris]

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

	A	B	C	D	E
1	CallDate	DialledNumber	CallingNumber	CallDuration
2	27/09/2018 10:34	01888888888	07333333333	0:04:47
3	26/09/2018 12:22	01999999999	07222222222	0:06:04
4	26/09/2018 11:50	01999999999	07111111111	0:06:13
5	19/09/2018 16:01	01888888888	07444444444	0:04:06
6	17/09/2018 12:43	01888888888	07555555555	0:19:59
7
8
9	Number	Longest Call	Longest Caller Number	Longest Call Date
10	01888888888	0:19:59	07555555555	17/09/2018	0:19:59
11	01999999999	0:06:13	07111111111	26/09/2018	0:06:13

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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.