exceldatatablesrelationshipfind-occurrences

Is there any way to seach for coincidences in Excel, in order to know how many times two different values have the same ID

I have a two tables like the following:

This is the first table. Here, I have an ID to refer the log, and some data about that particular register.

ID Content
1 Rock
2 Dust
2 Rock
2 Sand
3 Dirt
3 Sand
4 Rock
5 Rock
5 Sand
5 Dirt

This is the other table, that I want to create.

Rock Dirt Dust Sand
Rock NA 1 1 2
Dirt NA NA 0 2
Dust NA NA NA 1
sand NA NA NA NA

In summary, I want to create some kind of table that displays how many occurencies appear of two different elements with the same ID. In the example above with the table, if you imagine a really big database, I want my second table to display how many times "Rock" and "Sand" were together.

Btw, if you notice, half of the table is filled with "NA". That's because the table is mirrored. Also I unable the midline because, in my case, I don´t need to know if in the same log has two times the same element. Also, I have noticed that my second table isn´t very effective, I mean, the last row is usless if you see. Could you thing a better way to do the thing that I want?

I tried to use querys, formulas, and a combination of them, but I didn´t figured out how to do it.

Solution

  • This was not a quick fix. Took many hours.

    =IF($D2=E$1;"";SUM((FILTER($B$2:$B$11;ISNUMBER(MATCH($A$2:$A$11;TRANSPOSE(FILTER($A$2:$A$11;$B$2:$B$11=$D2));0)))=E$1)*1))

    or with LET:

    =LET(ID;$A$2:$A$11;Content;$B$2:$B$11;IF($D2=E$1;"";SUM((FILTER(Content;ISNUMBER(MATCH(ID;TRANSPOSE(FILTER(ID;Content=$D2));0)))=E$1)*1)))

    enter image description here

    To avoid dublicates you can arrange your data like this:

    enter image description here