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.
CodePudding user response：
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)))
To avoid dublicates you can arrange your data like this:
