Count Values which Co-Occur with one another

[clemelin]

I am the director for a nonprofit, volunteer ambulance with about 60 volunteers. I am trying to develop a way to see which pairs of volunteers work together the most (respond to the most calls together).

My data is formatted as follows:

Column 1: Run Number (unique to each call)
Column 2: Volunteer Name

The run number is repeated in a new row for every volunteer on the call Put another way, my data set has a row for each unique combination of runs / volunteers (5000+ rows). So it looks something like this:

18-11111 John Doe
18-11111 Mary Jane
18-11111 George McGregor
18-22222 Mary Joe
18-22222 John Smith
18-33333 John Doe
18-33333 George McGregor

My goal is to have something that would show me (in this example) that John Doe & George McGregor responded to two calls together. I have tried considering ways to do this via a Pivot Table or formula but cannot come up with anything.

[mehmetcik]

Hi

This is an interesting project for a good cause.

I have solved this for you.

I am in the process of making it bullet proof.

Will be back shortly.


Make sure row 1 is empty and your data starts in row 2

Ok:

Paste this formula in C2 and fill down =IF(AND(A2=A3,A3=A4),B2&": " &B4,IF(A2=A1,B1 & ": " &B2,""))

Paste this formula in D2 and fill down =IF(OR(ISERROR(INDEX($C$2:$C$2000,MATCH(0,COUNTIF($D$1:D1,$C$2:$C$2000),0))),INDEX($C$2:$C$2000,MATCH(0,COUNTIF($D$1:D1,$C$2:$C$2000),0))=0),"",INDEX($C$2:$C$2000,MATCH(0,COUNTIF($D$1:D1,$C$2:$C$2000),0)))

Activate this formula by selecting D2, Clicking into the formula box. Press and hold Ctrl, Press and hold shift, Press Enter and release all keys.

Now Fill the formula in D2 down until you get empty rows.

Paste this formula in E2 and fill down =COUNTIF($C$2:$C$200,D2)

'*************************************************************************************************************************************************

This Macro does all the work for you:

I am adding explanations so you can maintain this yourself.

I created the formulas in Excel and then recorded myself entering them into the cells using the Macro Editor.
I then modified the recorded macro so that I could make the Last Row LR dynamic

Please Login or Register  to view this content.

[KOKOSEK]

Impressive. But in case of 4 rows with the same ID works incorrectly (I don't know if this case can appear).

[mehmetcik]

Thanks KOKOSEK

I am waiting to see if that is possible.

I guess the formula should not be impossible.

There would be six combinations oo it might need another Column Though. Failing that a UDF.

[jason.b75]

Not sure how practical this would be with the real data, but it works.

Run Numbers in A2:A8, Volounteer Names in B2:B8

Pivot table started in E1
Pivot columns:- Voloutnteer Name
Pivot rows:- Run Number
Pivot Values:- Count of Run Number

Copy the Names from the pivot table (F2:J2) and paste to F10:J10
Copy the names again, and Pastespecial > Transpose to E11:E15 so that you have names as row and column headings for a new table.

Enter this formula into F11, then drag right and down to populate the table.

@ mehmetcik, any thoughts for improvement?
I'm wondering if it can be simplified in PQ but don't have a clue where to start with that.

[GeoffW283]

Here is a non-VBA attempt. Source data formatted per the OP's initial post is entered in blue-shaded columns A & B. This is the only necessary data entery - all other columns are calculated. All possible pair combinations are computed in column-J. The number of times each pair is present on the same call (the required answer) is computed in column-K.

Notes:

1) It is necessary to enter "zz" after the last call ID in column-A.

2) It is assumed that the call-ID / participant data is sorted by Call-ID.

3) Formulas have been copied down as far as necessary to support up to 60 participants/volunteeers. A maximum of 500 unique calls and an average of 3 volunteers per call has been assumed. If these limits are extended much further then Excel on my 6GB laptop becomes unstable.

A brief explanation of helper columns C thru I:

Column-C helps towards creating a consolidated list of participant names per call

Column-D is a list of unique distinct call-IDs and column-E is the corresponding comma separated list of participant names.

Column-F is a list of unique distinct participant names. Columns G & H list all possible pairs of names (if there are N names then there are N^2 pairs).

Column-I eliminates pairs where the first name and second name of the pair are the same and puts pairs in alphabetic order so that John, Fred becomes Fred, John. Finally column J is column-I with duplicates eliminated to yield the n!/((n-2)!2! valid name pair combinations. Finally, column-K then computes how many times each of these pairs have been on the same call.

See the attached workbook.