If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Longest Call, Longest Call Phone Number and Longest Call Date

Thread starter drewmorris
Start date Oct 2, 2018
Tags

call formula longest number phone numbers

D

drewmorris

New Member

Oct 2, 2018

#1

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

Copy a format multiple times

Click here to reveal answer

Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Sort by date Sort by votes

Peter_SSs

MrExcel MVP, Moderator

Oct 2, 2018

#2

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

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?

Upvote 0

sandy666

Banned - Rules violations

Oct 2, 2018

#3

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

D

drewmorris

New Member

Oct 2, 2018

#4

Peter_SSs said:
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

Number Longest Caller Number Longest 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

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.

Upvote 0

You must log in or register to reply here.

Similar threads

J

Question

Formula for pulling specific information from a cell with too much and varying characters

Replies: 2

Views: 524

Jun 8, 2021

Sufiyan97

W

Question

Creating a date/time formula that excludes weekends including friday

Replies: 5

Views: 745

Sep 5, 2019

Eric W

B

Question

Count number of months per year between two dates

Replies: 1

Views: 1K

Sep 20, 2019

DanteAmor

K

Question

formula help, please?

Replies: 2

Views: 600

Nov 8, 2018

kins

K

P

Question

Count of calls that are active in one hour slots

Replies: 1

Views: 498

Jun 12, 2018

Marcelo Branco

M

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,224,822

Messages: 6,181,165

Members: 453,021

Latest member: Justyna P

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top