phone call data - findng common numbers called or recieved between different callers in pivot

[adnan_003]

Hi, I’m a newbie, with mid-level excel experience. I am an analyst and need to analyse phone call data records.
I have solved my problem half way in pivot but wish to make it better as it is not fit for purpose and time consuming and hard to read.
See table 1 below for raw dummy data.

Pivot structure so far: (50% there)
Rows = called no and name 1
Columns = received no and name 2
Value = ref and is a count not sum.

PROBLEM 1:
Noshi calls adder 3 times
Adders call noshi 3 times
The total is 6, however the data is in different columns and hard to read. I have to manually add the total. I would like a total. In reality (see table 1), adders call 3 times and noshi receives 3 times. Noshi also calls 3 times and adders receive 3 times. A way to have a total for 2 way interaction would be useful that equals 6?

PROBLEM 2:
I need to identify common numbers called or received by either party. For example 456 is a common number, 999 and 656 are not. Noshi and adders call 456, but this could be reversed or one side called this number and the other side received from this number, it does not matter, it is a common number. I manually try to see if a number has value in both noshi and adders columns and rows. Is there is a better way to find common numbers called in pivot to identify patterns.

PROBLEM 3:
This is related to the above problem in pivots. Receiving number and name 2 goes in the columns; If I have hundreds of numbers excel can’t handle this and gives an error to many entries. Any solutions to get want I want without this problem? Changing pivot layout?

In real life I may have 20-50 phone attributed to people and thousands of line of data to analyse. Pivot seems ideal for this purpose but happy to try others ideas if you got ideas? Need to analyse data so I will have date and time fields also and need to look at the data to find patterns?

Thank you very much
Adnan


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ref[/TD]
[TD]name 1[/TD]
[TD]calling no[/TD]
[TD]receiving no[/TD]
[TD]name 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]adders[/TD]
[TD]3333[/TD]
[TD]1234[/TD]
[TD]noshi[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]adders[/TD]
[TD]3333[/TD]
[TD]1234[/TD]
[TD]noshi[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]noshi[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1234[/TD]
[TD]3333[/TD]
[TD]adders[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]noshi[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1234[/TD]
[TD]3333[/TD]
[TD]adders[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]adders[/TD]
[TD]3333[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]noshi[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1234[/TD]
[TD]3333[/TD]
[TD]adders[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]noshi[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1234[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD]noshi[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1234[/TD]
[TD]999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]656[/TD]
[TD]1234[/TD]
[TD]noshi[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]adders[/TD]
[TD]3333[/TD]
[TD]1234[/TD]
[TD]noshi[/TD]
[/TR]
</tbody>[/TABLE]

 

[adnan_003]

sorry for got to say, a step by step guide on pivot would help, also im using excel 2003 at work!

 

[inactiveuserps07]

Make sure your data is formatted as a TABLE.

Add a column to the right of "Name 2" called, for instance, "Common."

Assuming your table headers are exactly as show above, insert the following formula into the top cell in the "Common" column:

=COUNTIFS([name 1],[@[name 2]],[name 2],[@[name 1]],[calling no],[@[receiving no]],[receiving no],[@[calling no]])+COUNTIFS([name 1],[@[name 1]],[name 2],[@[name 2]],[calling no],[@[calling no]],[receiving no],[@[receiving no]])

This will give you the number of times this call went either way between the two parties from those phone lines.

 

[adnan_003]

Thanks Erik, much obliged for taking time to help.
I had to amend YOUR formula, as I need to know the people calling and receiving I don’t have names for also. Name field is - if I know who the number belongs to.

=COUNTIFS([calling no],[@[receiving no]],[receiving no],[@[calling no]])+COUNTIFS([calling no],[@[calling no]],[receiving no],[@[receiving no]])

The problem is this creates many duplicates totals and I can’t find a way to look for common numbers or how this could be used in pivots for much larger data sets or how I would filter this? 3 and 3 is still a better way to know the breakdown just in separate columns causes the problem.

I can use this pivot for quick and dirty analysis to find totals but for more larger data sets I need to use pivots. I assume I will need to do something first in excel before I create the pivot and also I need use date and time fields to analyse which come after the ref field.

But good try mate, but i need a formula or way to find the common numbers also between 2 or more callers and stand out so I can do a fliter or find them! This will be tricky!
My pivot will contains ref, date, time, name 1, calling no, receiving no, name 2. Pivot works out the count but data is in different columns (see me pivot input fields)

Cheers
Adnan

 

[inactiveuserps07]

Hi Adnan,

Honestly, I'm not understanding what you need, even after reading everything. But I think what you are trying to do will require a full solution or system, not just formula help. In other words, I suspect that what you want to do may be beyond the scope of this free-help board, but rather something that you'd have to hire someone to work on with you.

Still, if you can provide actual worksheet images with what you HAVE, and also showing the desired RESULT you're looking for with formulas or VBA, it would help viewers here better make sense of what you're trying to explain.

Erik

 

[adnan_003]

Hi Erik, no sure how else to explain, but I used the formula you gave and I made use of it of in other ways also.

Managed to get calling, receiving total and overall total in in 1 row. Pivot may still give me a problem if their is to many columns.
But I can use the table to find common numbers and reduce my list by removing duplicates and using filters on calling and receiving numbers to do this.

Formula below for interest.

Grateful for the help to get me started and I made a way to make it work 'cowboy style'. Even if this makes no sense, you helped a lot, so thank you.

TOTAL: CALLING AND RECEIVING CALLS
=COUNTIFS([calling no],[@[receiving no]],[receiving no],[@[calling no]])+COUNTIFS([calling no],[@[calling no]],[receiving no],[@[receiving no]])
CALLING TOTAL:
=COUNTIFS([receiving no],[@[receiving no]],[calling no],[@[calling no]])
RECEIVING TOTAL:
=COUNTIFS([calling no],[@[receiving no]],[receiving no],[@[calling no]])

 

[inactiveuserps07]

Hi, Adnana. Yes, I built the "common" formula by first building the "call" formula and "receive" formula as you have above, and then merging them. I didn't think you were in need of the one-way formulas; but I'm glad you were able to backtrack them.

Little by little, it'll come together, I'm sure!

 

[adnan_003]

Hi again! I thought I had this fixed..I did the above in office 2010. However at work I use 2003 excel!.. not sure how to create table or use this formula..it gives me error that name not valid, and 2003 does not have countifs function Only count if...

Can anyone help me use this formula in excel 2003 , step by step or how to fix formula?

TOTAL: CALLING AND RECEIVING CALLS
=COUNTIFS([calling no],[@[receiving no]],[receiving no],[@[calling no]])+COUNTIFS([calling no],[@[calling no]],[receiving no],[@[receiving no]])
CALLING TOTAL:
=COUNTIFS([receiving no],[@[receiving no]],[calling no],[@[calling no]])
RECEIVING TOTAL:
=COUNTIFS([calling no],[@[receiving no]],[receiving no],[@[calling no]])