sp2 sp2 - 1 month ago 5x
R Question

Comparing multiple rows and creating a matrix in R or in Excel

I have a file containing, multiple rows as follows

In file1:

a 8|2|3|4 4
b 2|3|5|6|7 5
c 8|5|6|7|9 5

a to a has 4 overlaps, similarly a to b had 2 overlaps, so to check the overlaps between various entity,I need to generate a matrix with the above details, and the output should be a matrix like

a b c
a 4 2 1
b 2 5 3
c 1 3 5

Please give me a suggestion, how to do this? this there anyway to do this using excel or using a shell script or using R? I have written this following code but since I am not a good coder, cudn't get the output printed in a right format.

Newmet<-sapply(newmet2, function(x) x[2:length(x)], simplify=F )

for (i in 1:length(Newmet))
for (j in 1:length(Newmet)
c <- ((intersect(Newmet[[i]], Newmet[[j]]))
print (length(c))


If the numbers are in separate cells starting in Sheet1!A1, try


starting at Sheet2!A1.

Must be entered as an array formula using CtrlShiftEnter

Alternative formula that doesn't have to start at Sheet2!A1


enter image description here