Need to transform columns-lists into a coincidence matrix (array)

[AusterRulez]

I have a table, in Excel, with three columns 1)list of individuals, 2)different time points and 3) their locations, which I need to transform into an array (coincidence matrix) by scoring the number of times two individual are simultaneously at the same place. The Table has more than 13,000 rows. have tried CONCATENATION OF Time&Place and used a Pivot Table but I could not figure out the way to generate the array. The main diagonal should be the number of occurrences of each individual. Unfortunately, I have not experience with Power Query.

 

[Palo]

Use Pivot table

 

[Fluff]

@Palo it would be helpful if you explained how. Especially as the OP has said they don't know how to do it with a pivot table.

 

[Cubist]

This gives most of the correct count except one cell - potentially a typo on your end (in red). Not sure how it'll perform on larger data set.

Book1
	A	B	C	D	E	F	G	H	I
1	Ind.	Time	Place			I1	I2	I3	I4
2	I1	11:00	A		I1	3
3	I2	11:00	A		I2	1	1
4	I3	12:00	A		I3	1	0	2
5	I4	12:00	A		I4	1	0	2	2
6	I1	11:00	B
7	I1	13:00	C
8	I3	13:00	C
9	I4	13:00	C
Sheet3

Cell Formulas
Range	Formula
F1:I1	F1	=TOROW(E2#)
E2:E5	E2	=UNIQUE(A2:A9)
F2:I5	F2	=LET( a,A2:A9,u,UNIQUE(a),t,B2:B9&C2:C9,rw,ROWS(u), MAKEARRAY(rw,rw,LAMBDA(r,c,IF(r>=c,SUM(MAP(UNIQUE(t),LAMBDA(m,LET(f,FILTER(a,t=m),INT(SUM((INDEX(u,r)=f)+(INDEX(u,c)=f))/2))))),""))) )
Dynamic array formulas.

 

[AusterRulez]

@Cubist Awesome !Thanks. I

 

[Cubist]

You're welcome.

 

[AusterRulez]

Cubist formula does not work with the originial dataset due to memory bottleneck (I'm currently using24GB RAM on a macbookpro M2). Any ideas? Thanks in advance.

 

[Automatrix]

It's actually not too hard to do in Power Query just using the UI.

let
    Source = Excel.CurrentWorkbook(){[Name="your_data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Individual", type text}, {"Place", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Time", "Place"}, #"Changed Type", {"Time", "Place"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Individual"}, {"Individual.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Changed Type", {"Individual", "Individual.1"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Individual.1]), "Individual.1", "Count", List.Sum)
in
    #"Pivoted Column"

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.

www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.

www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...

excel.solutions

 

[Cubist]

Cubist formula does not work with the originial dataset due to memory bottleneck (I'm currently using24GB RAM on a macbookpro M2). Any ideas? Thanks in advance.

Formulas have their limitations a your ask is quite niche and the native functions are't built to do something like that thus it's very inefficient. Your best bet is to use VBA or PowerQuery.