matrixgoogle-sheets-formulalookup-tables# (Google Sheets) How to find column 1 and row 1 values for a matrix of occurrences in which both are present in data

( *edit: Rough idea of what I'm trying to do: https://docs.google.com/spreadsheets/d/12rIUiuE-TSB0E3XRSnQ0YU9BCpuTsHfrUZXgCvxOE44/edit#gid=2100307022* )

I feel like this should have a really simple solution, but I can't work it out...

Basically, I am analysing a table of tagging data in which more than one tag can be applied to the same record. I am interested in seeing which tags occur together most frequently, so I have created a matrix as such, showing tags in the first column and row, and their co-occurrence in the count matrix below:

Simplified version of matrix - the version I'm using has 60 X and 60 Y items

Below it, I've pulled the highest numbers from the matrix (i.e. where the X and Y values appear the most - 5 in the above case), but am really struggling to work out how to simply create a function that will find the column and row header values for each.

Essentially, I ultimately want e.g. in this case:

**5: A & D** (where 5 = number of times A and D occur together)
(plus any others where the number of co-occurrences is greater than X)

It's highly possible that I got stuck in the woods with this one and there's an easier way to do this, or that I'm just overlooking a simple formula I could use, but at the moment I can't think of a code to create for this that wouldn't be incredibly long or time-consuming.

I've thought of ways to do it with MATCH and INDEX, but I'm not particularly strong with these functions tbh and can't work out how to use them in a 2-D array like this. I've also thought about using combinations of FILTER, FLATTEN and different LOOKUPS, but likewise can't work it out.

Any help would be much appreciated.

Solution

*Here's one approach you may test out:*

```
=let(Σ,sort(unique(reduce(,O2:X11,lambda(a,c,vstack(a,{c,torow(sort(tocol({index(N1:N11,row(c)),index(A1:X1,column(c))}),1,))})))),1,),
filter(Σ,index(Σ,,1)>=3,--index(Σ,,1)))
```

**X**value is taken as`>=3`

for test purposes

- Print 2-D Array in clockwise expanding spiral from center
- Assign values based on reverse combination of two columns
- R - Reshape list with matrices
- How to split a square matrix into cubes without loops
- Pretty print 2D list?
- Why are quaternions used for rotations?
- Multiple values in one tile with geom_tile
- How to put number of a particular element in a particular row and column constraints in a matrix?
- How to calculate a Kernel/Matrix efficiently
- What's the best way to create a "3D identity matrix" in Numpy?
- How to compute exponential of a matrix inside CUDA thread?
- How to get element-wise matrix multiplication (Hadamard product) in numpy?
- Why does dim=1 return row indices in torch.argmax?
- How to unnest a "list" in a martix?
- Can not update matrix entries for large matrices
- How do I make a matrix from a list of vectors in R?
- Sum two array rows in PHP on a single line
- Get indices of matrix from upper triangle without numpy
- How to read scalar, vector and matrix information in string format resembling Python syntax in R?
- Cache-friendly sqare matrix transposition logic issue
- How to select winner of condorcet election via matrix?
- solve rectangular matrix in python to get solution with arbitrary parameters
- Sum of a list of matrices in R
- Submatrix Ocaml
- Parameterizing type definition at compile time
- How to make a symmetric matrix where the sum of each row and column is k when the diagonal elements are certain (not zero) in excel?
- MPI4PY: Scatter a matrix
- Fill the Diagonal of Each Matrix in a 3D numpy Array with a Vector
- Rotate UVs in Vertex Shader without distorting texture
- Why do I divide Z by W in a perspective projection in OpenGL?